Match two columns with one column

[shield5]

Hi,

I have three long columns of data that look like this:

A B C
Sam Tim 24
Tim
Lee Sue 36
Ann
Sue Lee 48

my goal is to match columns B and C with column A.

Is there a macro I can use to match them up side-by-side and make them look like this?

A B C
Sam
Tim Tim 24
Lee Lee 48
Ann
Sue Sue 36


Thanks

 

[MickG]

Try this:-
NB:- Actual data assumed to start on row2.

[COLOR="Navy"]Sub[/COLOR] MG01Jan37
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Set[/COLOR] Rng = Range("A2", Range("A" & Rows.Count).End(xlUp))
[COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
ReDim Ray(1 To Rng.Count, 1 To 3)
    [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
        c = c + 1
        .Item(Dn.Value) = c
        Ray(c, 1) = Dn.Value
    [COLOR="Navy"]Next[/COLOR]
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng.Offset(, 1)
    [COLOR="Navy"]If[/COLOR] .exists(Dn.Value) [COLOR="Navy"]Then[/COLOR]
        Ray(.Item(Dn.Value), 1) = Dn.Value
        Ray(.Item(Dn.Value), 2) = Dn.Value
        Ray(.Item(Dn.Value), 3) = Dn.Offset(, 1).Value
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] Dn
Rng.Resize(, 3) = ""
Range("A2").Resize(c, 3) = Ray
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[shield5]

Thank you very much it worked perfectly.

 

[MickG]

You're welcome