Multi line transpose

[talonds]

Hi,

I have been trying to come up with a way to transpose 2 cols. of data into a horizontal format, using the 1st column as the index, i have tried a combination of paste special and pivots, but to no avail, so i'm guessing VBA is the way, i hope not though.
B - C
1 2
3
4
5 6
7
8
9
to

e - f - g - h - i - j
1 2 3 4
5 6 7 8 9

e.t.c

Many Thanks,

Dan.

 

[MickG]

Try this for results starting "E1".

[COLOR="Navy"]Sub[/COLOR] MG24Nov43
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Ac [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("B1"), Range("B" & Rows.Count).End(xlUp))
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    [COLOR="Navy"]If[/COLOR] Dn.Offset(, 1).Value <> "" [COLOR="Navy"]Then[/COLOR]
        Ac = 5: c = c + 1
        Cells(c, Ac) = Dn.Value: Cells(c, Ac + 1) = Dn.Offset(, 1).Value
        Ac = Ac + 1
    [COLOR="Navy"]Else[/COLOR]
        Ac = Ac + 1
        Cells(c, Ac) = Dn.Value
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[talonds]

Hi, thank you for your reply, unfortunately, i have made a mistake with my example, didn't check how the msg posted. should have been.
B - C
1 - 2
--- 3
--- 4
5 - 6
--- 7
--- 8
--- 9
to

e - f - g - h - i - j
1 - 2 - 3 - 4
5 - 6 - 7 - 8 - 9