Data format

[triciaodd]

I have data which looks like this

John pink
blue
red
James green
white
brown
yellow

and I would like it to look like this

John pink blue red
James green white brown yellow

Is there a quick way of achieving this without having to cut and paste? I have 500 rows that need to be changed so a quick and simple fix would be much appreciated.

PS - Having looked at the post it shows differently. Essentially the colours are in column 2 with the names in column 1 and I want them all to be in one row and not under each other in the column. I hope I have explained this properly.

[raypayette]

It's difficult to understand.
The first group has 3 rows and the second has 4; is this always the case?
Do you want the colors grouped with the name in the same column or in different columns?

[triciaodd]

Thanks for your interest.

I want the colours in the same row as the name but each colour in a separate column. Sometimes there are three colours, sometimes there are four.

I have attached a snapshot of a spreadsheet showing it better. The first bit shows how it is and the second shows how I want it to be.

Hope that explains things better.

[triciaodd]

Not sure if attachment went first time. I hope it did this time.

Thanks

[raypayette]

Sub compact()
ReDim ar(5) As String
For i = 7 To 1 Step -1
a = Cells(i, 1)
j = j + 1
b = Cells(i, 2)
If a = "" Then
ar(j) = b
Rows(i & ":" & i).Select
Selection.Delete Shift:=xlUp
Else
Cells(i, 2) = ar(1)
Cells(i, 3) = ar(2)
Cells(i, 4) = ar(3)
Cells(i, 5) = ar(4)
Cells(i, 6) = ar(5)
ReDim ar(5)
j = 0
End If
Next
End Sub

HTH