Merge duplicate rows into one!

[MACEOCH]

Hi guys,

I'm doing some medical research and I'm having difficulty arranging my excel data. I'm sure what I need done is very straightforward but I'm still only learning the basics of excel so I was wondering if someone wouldn't mind taking a quick look!

My Data is arranged in two columns as follows-

A	1
A	2
A	3
B	4
B	5
C	6
D	7
E	8
E	9
E	10

<tbody>
</tbody>

and I need to rearrange it so it looks like the following:

A	1	2	3
B	4	5
C	6
D	7
E	8	9	10

<tbody>
</tbody>

I'm sure this can't be too difficult! I played around at it for about an hour but to be honest I think I'm stumped!
Thanks a million.

Dr C

 

[MickG]

Try this:-
Results Start "F1"

[COLOR="Navy"]Sub[/COLOR] MG25May07
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Q [COLOR="Navy"]As[/COLOR] Variant, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Ray(), oMax [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
   [COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
            .CompareMode = vbTextCompare
    [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
        [COLOR="Navy"]If[/COLOR] Not .Exists(Dn.Value) [COLOR="Navy"]Then[/COLOR]
            n = n + 1
            ReDim Preserve Ray(1 To Rng.Count, 1 To n)
            .Add Dn.Value, Array(2, n)
                Ray(1, n) = Dn.Value
                Ray(2, n) = Dn.Offset(, 1)
        [COLOR="Navy"]Else[/COLOR]
            Q = .Item(Dn.Value)
               Q(0) = Q(0) + 1
               Ray(Q(0), Q(1)) = Dn.Offset(, 1)
               oMax = Application.Max(oMax, Q(0))
            .Item(Dn.Value) = Q
        [COLOR="Navy"]End[/COLOR] If
    [COLOR="Navy"]Next[/COLOR]
Range("F1").Resize(n, oMax) = Application.Transpose(Ray)
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[MACEOCH]

Fantastic! Thank you so much, you really helped me out!

Kind regards and best wishes
Dr C

 

[MickG]

You're Welcome