| Forum Home | ||||
| Press F1 | ||||
| Thread ID: 93979 | 2008-10-09 01:33:00 | Spreadsheet question - Is this possible? | Morgenmuffel (187) | Press F1 |
| Post ID | Timestamp | Content | User | ||
| 710924 | 2008-10-09 01:33:00 | Ok i normally do this via an mysql database, but quite frankly uploading the data and writing the functions to output things correctly is a bit time consuming, especially as the source material and the output material end up being put into spreadsheets Apart from the real basics i am not that good at spreadsheets I have excell 2007, Openoffice and planmaker 2006 heres the problem I have spreadsheet A which contains the following in a single column Blue Red Yellow Green I have Spreadsheet B I am using pipes (|) to seperate the Fields, www,socks.com | socks www,undies.com | undies www,shoes.com | shoes www,hose.com | hose What i want it to generate is www,socks.com | Blue socks www,undies.com | Blue undies www,shoes.com | Blue shoes www,hose.com | Blue hose www,socks.com | Red socks www,undies.com | Red undies www,shoes.com | Red shoes www,hose.com | Red hose www,socks.com | Yellow socks www,undies.com | Yellow undies www,shoes.com | Yellow shoes www,hose.com | Yellow hose www,socks.com | Green socks www,undies.com | Green undies www,shoes.com | Green shoes www,hose.com | Green hose Is this doable via a spreadsheet function? or should i just stick with the database |
Morgenmuffel (187) | ||
| 710925 | 2008-10-11 08:47:00 | Hi Nigel, good win for the Stags tonight. If I understand you correctly for each row of your data your wanting to add a colour, so each row item becomes 4 items (one for each colour). You can use the Search function to determine the left portion of the text (up to the pipe) and the right portion of the text (after the pipe) as follows... Left text =LEFT(A1,SEARCH( " | " ,A1,1)) Right text =RIGHT(A1,LEN(A1) - SEARCH( " | " ,A1,1)) Both together adding the colour Blue to the text =LEFT(A1,SEARCH( " | " ,A1,1)) & " Blue " & RIGHT(A1,LEN(A1) - SEARCH( " | " ,A1,1)) You could also use a macro to loop through the target cells and create the new values. The following example assumes the colours are in sheet1 cells A1:A4 and the target data is in Sheet2 cells A1:A4 and you want the result to be populated to Sheet2 column B. Sub Example() Dim rngColour As Range, rngTarget As Range, rngEachColourCell As Range, rngEachTargetCell As Range Dim strLeftText As String, strRightText As String, n As Integer 'The cells that has the colours you want to add to the data Set rngColour = Worksheets( " Sheet1 " ).Range( " A1:A4 " ) 'The cells that you want to add each colour value Set rngTarget = Worksheets( " Sheet2 " ).Range( " A1:A4 " ) 'Outer loop to loop through the target cells For Each rngEachTargetCell In rngTarget 'Inner loop to loop through the colour values to add each colour for every target cell For Each rngEachColourCell In rngColour 'Get left text portion up to the pipe strLeftText = Left(rngEachTargetCell, InStr(1, rngEachTargetCell, " | " , vbTextCompare)) 'Get right text portion after the pipe strRightText = Right(rngEachTargetCell, Len(rngEachTargetCell) - InStr(1, rngEachTargetCell, " | " , vbTextCompare)) 'increment an integer representing the next row to place the data n = n + 1 'Add text of left portion, single space, then colour, then right portion to the cells in Column B Worksheets( " Sheet2 " ).Range( " B " & n).Value = strLeftText & " " & rngEachColourCell.Value & strRightText Next rngEachColourCell Next rngEachTargetCell End Sub regards, Graham |
Parry (5696) | ||
| 710926 | 2008-10-12 22:11:00 | Thanks for that Parry, I'll try that out later on today | Morgenmuffel (187) | ||
| 1 | |||||