Search for all the values on a list and copy a value from the same row for each match

[kusacw]

Hi there!

Well I have two databases with people in them, each database has different information, and they don't necessarily have all the same people, but they both have ID numbers.

So what I want to do, is see which ID numbers from Database 1 are in Database 2. This is the first part which should not be too complicated.

The second thing I want to do, is if an ID number from Database 1 is found on Database 2, I want to extract a value from the same person but from a different column.

Explained in other words: Say I have on Database 1 that has Name and Address, and on Database 2 I have Name and Age. What I would like is to see which people from Database 1 are ALSO on Database 2, and I want to know their ages, if possible in a new list/sheet/document that shows Name and Age. So the final document would be the people that are on Database 1 AND Database 2, with their ages.

Thanks in advance!

Kusac

[AlvaroSiza]

Which column do the ID #s appear in DB1? What is the Name of this worksheet?
Which column do the ID #s appear in DB2? What is the Name of this worksheet?
Which column of which worksheet holds the Ages?

Answer these and we'll be on our way.

[kusacw]

Well that information I do not have yet, I just wanted to learn how it could be done, because I will be required to do this soon... so I wanted to Learn. You can make them up if you like and let me know what the code or formula looks like. Thanks in advance Alvaro!

I could also create the sheets for learning purposes if you'd like.

Let's try with this:

Column A on DB1.xlsx has the ID number
Column B on DB2.xlsx has the ID number
Column C on DB2.xlsx has the age

and let's say I want the age to be added on Column C of DB1.xslx

[AlvaroSiza]

Generally speaking, and because we don't know the details yet, the following would (under the assumptions I have made) produce the result.

See my notes throughout.

Please Login or Register  to view this content.

[kusacw]

Understood, saved the code and going to try it out adding the information. You are very methodical and I understood pretty much all of it. Thanks, I will let you know how it goes.

When I try to run the macro it tells me that I have to fix something, starting on:

Set rngToSend = Range(rngCell).Resize(1, 2)
With ws3
Set rngDest = .Range("A20000").End(xlUp).Offset(1).Resize(1, 2)
End With
rngDest.Value = rngToSend.Value
End If
Next
Next

'Turn everything back on (efficiency)
With Application
.ScreenUpdating = True
.EnableEvents = True
.Calculation = xlCalculationAutomatic
End With

End Sub