Adding data to end of row based on A1 value

[Royalty13]

I'm working with a rather long list that I'm trying to add to the end of a spreadsheet. I know there are formula's out there that can go to the next empty cell in a row or a column, but I need a vba code that can go to the next empty cell in a row based on the first column's value.
For example:

Sample list:

Dog	Pitbull
Cat	Sphynx
Dog	Beagle
Bird	Parrot

<tbody>
</tbody>

Sample spreadsheet:

Bird	Toucan	Owl	Eagle
Dog	Rottweiler
Cat	Siamese

<tbody>
</tbody>

Final result:

Bird	Toucan	Owl	Eagle	Parrot
Dog	Rottweiler	Pitbull	Beagle
Cat	Siamese	Sphynx

<tbody>
</tbody>

Is there any easy way to do this?

 

[MickG]

With "Sample data" in sheet "Data" and "Sample Spreadsheet" in sheet "Results
Actual data starting rows 2.
Try this for update on sheet "Results":-

[COLOR="Navy"]Sub[/COLOR] MG24Apr07
[COLOR="Navy"]Dim[/COLOR] RngR [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Lst [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] RngD [COLOR="Navy"]As[/COLOR] Range, Q [COLOR="Navy"]As[/COLOR] Variant
[COLOR="Navy"]With[/COLOR] Sheets("Results")
[COLOR="Navy"]Set[/COLOR] RngR = .Range("A2", .Range("A" & Rows.Count).End(xlUp))
[COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] RngR
    Lst = Cells(Dn.Row, Columns.Count).End(xlToLeft).Column
    .Item(Dn.Value) = Array(Dn, Lst)
[COLOR="Navy"]Next[/COLOR]
[COLOR="Navy"]With[/COLOR] Sheets("Data")
    [COLOR="Navy"]Set[/COLOR] RngD = .Range("A2", .Range("A" & Rows.Count).End(xlUp))
[COLOR="Navy"]End[/COLOR] With
    [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] RngD
        [COLOR="Navy"]If[/COLOR] .exists(Dn.Value) [COLOR="Navy"]Then[/COLOR]
            Q = .Item(Dn.Value)
            Q(0).Offset(, Q(1)) = Dn.Offset(, 1).Value
            Q(1) = Q(1) + 1
            .Item(Dn.Value) = Q
        [COLOR="Navy"]End[/COLOR] If
  [COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[Royalty13]

Thanks Mick! That worked perfectly

 

[MickG]

You're welcome