View Full Version : Spreadsheet question - Is this possible?

09-10-2008, 02:33 PM
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


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

11-10-2008, 09:47 PM
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


13-10-2008, 11:11 AM
Thanks for that Parry, I'll try that out later on today