Ok, so I think I understand now, sorry I did not read it correctly the first time.
My solution is not elegant but I think you might be able to clean it up either by some macros or modifying the VB code which is beyond me.
Step 1.
Selection Column B and do text to columns, deliminated, other and enter Control J, finish.
this will give you this:
Blue, 1003, 1005, 1006
Red, 2550, 3440, 4444
Step 2.
give each column a header, does not water what it is we can delete it later.
Step 3.
Grab the macro from here:
https://stackoverflow.com/questions...-into-column-with-multiple-rows-in-excel-2007
<code>Sub NormalizeSheet()
Dim wsOriginal As Worksheet
Dim wsNormalized As Worksheet
Dim strKey As String
Dim clnHeader As Collection
Dim lngColumnCounter As Long
Dim lngRowCounterOriginal As Long
Dim lngRowCounterNormalized As Long
Dim rngCurrent As Range
Dim varColumn As Variant
Set wsOriginal = ThisWorkbook.Worksheets("Original") 'This is the name of your original worksheet'
Set wsNormalized = ThisWorkbook.Worksheets("Normalized") 'This is the name of the new worksheet'
Set clnHeader = New Collection
wsNormalized.Cells.ClearContents 'This deletes the contents of the destination worksheet'
lngColumnCounter = 2
lngRowCounterOriginal = 1
Set rngCurrent = wsOriginal.Cells(lngRowCounterOriginal, lngColumnCounter)
' We'll loop through just the headers to get a collection of header names'
Do Until IsEmpty(rngCurrent.Value)
clnHeader.Add rngCurrent.Value, CStr(lngColumnCounter)
lngColumnCounter = lngColumnCounter + 1
Set rngCurrent = wsOriginal.Cells(lngRowCounterOriginal, lngColumnCounter)
Loop
'Here we'll reset our Row Counter and loop through the entire data set'
lngRowCounterOriginal = 2
lngRowCounterNormalized = 1
lngColumnCounter = 1
Do While Not IsEmpty(wsOriginal.Cells(lngRowCounterOriginal, lngColumnCounter))
Set rngCurrent = wsOriginal.Cells(lngRowCounterOriginal, lngColumnCounter)
strKey = rngCurrent.Value ' Get the key value from the current cell'
lngColumnCounter = 2
'This next loop parses the denormalized values for each row'
Do While Not IsEmpty(wsOriginal.Cells(lngRowCounterOriginal, lngColumnCounter))
Set rngCurrent = wsOriginal.Cells(lngRowCounterOriginal, lngColumnCounter)
'We're going to check to see if the current value'
'is equal to NULL. If it is, we won't add it to'
'the Normalized Table.'
If rngCurrent.Value = "NULL" Then
'Skip it'
Else
'Add this item to the normalized sheet'
wsNormalized.Range("A" & lngRowCounterNormalized).Value = strKey
wsNormalized.Range("B" & lngRowCounterNormalized).Value = clnHeader(CStr(lngColumnCounter))
wsNormalized.Range("C" & lngRowCounterNormalized).Value = rngCurrent.Value
lngRowCounterNormalized = lngRowCounterNormalized + 1
End If
lngColumnCounter = lngColumnCounter + 1
Loop
lngRowCounterOriginal = lngRowCounterOriginal + 1
lngColumnCounter = 1 'We reset the column counter here because we're on a new row'
Loop
End Sub
Make sure that you either update the macro to your sheetnames or update your sheet names. The macro will fail if the data does not have headers (I have not tried to fix this). Also make sure you have added a 2nd sheet where the data will be copied to.
Run the Macro and you will end up with this:
</code>
Blue | header1 | 1 |
Blue | header2 | 2 |
Blue | header3 | 3 |
red | header1 | 4 |
red | header2 | 5 |
red | header3 | 6
|
red | header4 | 7 |
<colgroup><col style="width:48pt" span="3" width="64"> </colgroup><tbody>
</tbody>
You could update the macro to auto delete the unessary column if needed.
Like I said its not pretty, but once the macro is working for you it should work nicely (you could even build a macro to do the test columns and insert the header row once its all working for you so everything can be done in 1 click.
HTH
Cameron