data
 

I have the following data in one worksheet:

abc red 1
abc blue 2
abc green 3
edf red 3
edf orange 5
edf purple 4

I need a formula to do the following sort in new worksheet:

abc red 1 abc blue 2 abc green 3
edf red 3 edf orange 5 edf purple 4

When I use the Vlookup formula, it only searches the first top results, but
I need it to search the next on at the bottom, until there isn't any abc or
edf left.

data
 

Say your original data is in Sheet1 and the destination is Sheet2. This this
small macro:

Sub reorderThem()
Set s1 = Sheets("Sheet1")
Set s2 = Sheets("Sheet2")
s1.Activate
n = Cells(Rows.Count, "A").End(xlUp).Row
Range("A1:C1").Copy s2.Range("A1")
i = 1
j = 4
For k = 2 To n
If Cells(k, 1).Value < Cells(k - 1, 1).Value Then
i = i + 1
j = 1
End If
Range("A" & k & ":C" & k).Copy s2.Cells(i, j)
j = j + 3
Next
End Sub


Starting with:

alpha green 4
alpha happy 45
alpha ugly 99
beta first 1
beta second 2
beta third 3
gamma xx 1
gamma yy 2
zeta qwerty poiuy
zeta ytrewq yuiop


It will make:

alpha green 4 alpha happy 45 alpha ugly 99
beta first 1 beta second 2 beta third 3
gamma xx 1 gamma yy 2
zeta qwerty poiuy zeta ytrewq yuiop

--
Gary''s Student - gsnu200825


"JAY" wrote:

I have the following data in one worksheet:

abc red 1
abc blue 2
abc green 3
edf red 3
edf orange 5
edf purple 4

I need a formula to do the following sort in new worksheet:

abc red 1 abc blue 2 abc green 3
edf red 3 edf orange 5 edf purple 4

When I use the Vlookup formula, it only searches the first top results, but
I need it to search the next on at the bottom, until there isn't any abc or
edf left.