Combine rows based off column id

[hcanning]

Hi ,
I'd like to know how to merge rows based off unique id values. Lets say I have:
[TABLE="width: 500"]
<tbody>[TR]
[TD]ID[/TD]
[TD]Name[/TD]
[/TR]
[TR]
[TD]@345[/TD]
[TD]Jack[/TD]
[/TR]
[TR]
[TD]@532[/TD]
[TD]Mary[/TD]
[/TR]
</tbody>[/TABLE]

and another sheet with
[TABLE="width: 500"]
<tbody>[TR]
[TD]ID[/TD]
[TD]Age[/TD]
[/TR]
[TR]
[TD]@345[/TD]
[TD]32[/TD]
[/TR]
[TR]
[TD]@532[/TD]
[TD]38[/TD]
[/TR]
</tbody>[/TABLE]
...
I'd like to be able to combine sheets based off id to append data
[TABLE="width: 500"]
<tbody>[TR]
[TD]ID[/TD]
[TD]Name[/TD]
[TD]Age[/TD]
[/TR]
[TR]
[TD]@345[/TD]
[TD]Jack[/TD]
[TD]32[/TD]
[/TR]
[TR]
[TD]@352[/TD]
[TD]Mary[/TD]
[TD]38[/TD]
[/TR]
</tbody>[/TABLE]
...my real wold example will be comparing 500 rows or so and appending numerous columns based off the id
Thanks

 

[Fluff]

Hi & welcome to the board.
How about this

Sub GetInfo()
   Dim Ws1 As Worksheet
   Dim Ws2 As Worksheet
   Dim Cl As Range
   
   Set Ws1 = Sheets("[COLOR=#ff0000]Sheet1[/COLOR]")
   Set Ws2 = Sheets("[COLOR=#ff0000]Sheet2[/COLOR]")
   With CreateObject("scripting.dictionary")
      For Each Cl In Ws2.Range("A2", Ws2.Range("A" & Rows.Count).End(xlUp))
         If Not .exists(Cl.Value) Then .Add Cl.Value, Cl.Offset(, 1).Value
      Next Cl
      For Each Cl In Ws1.Range("A2", Ws1.Range("A" & Rows.Count).End(xlUp))
         If .exists(Cl.Value) Then Cl.Offset(, 2).Value = .Item(Cl.Value)
      Next Cl
   End With
End Sub

Change sheets names in red to suit.
This will take the data in Col B on sheet2 & add it to col C on sheet1

 

[hcanning]

Hi & welcome to the board.
How about this

Sub GetInfo()
   Dim Ws1 As Worksheet
   Dim Ws2 As Worksheet
   Dim Cl As Range
   
   Set Ws1 = Sheets("[COLOR=#ff0000]Sheet1[/COLOR]")
   Set Ws2 = Sheets("[COLOR=#ff0000]Sheet2[/COLOR]")
   With CreateObject("scripting.dictionary")
      For Each Cl In Ws2.Range("A2", Ws2.Range("A" & Rows.Count).End(xlUp))
         If Not .exists(Cl.Value) Then .Add Cl.Value, Cl.Offset(, 1).Value
      Next Cl
      For Each Cl In Ws1.Range("A2", Ws1.Range("A" & Rows.Count).End(xlUp))
         If .exists(Cl.Value) Then Cl.Offset(, 2).Value = .Item(Cl.Value)
      Next Cl
   End With
End Sub

Change sheets names in red to suit.
This will take the data in Col B on sheet2 & add it to col C on sheet1

Thanks...where do I run that. Excel novice.

 

[Fluff]

It needs to go in a Regular module. Have a look here for more details http://www.contextures.com/xlvba01.html