Match data and copy row

[cpointon]

Hi,

Sorry I'm new to VBA and having trouble with a macro. I have one workbook with two sheets (sheet1 and sheet2). Sheet one has a list of information in a number of columns and column A is the file number for each set of information. Sheet two has only file numbers.

I'm looking for a macro to match the file numbers and copy the information from sheet one to sheet2.

Normally I would use Vlookup but in this case, sheet 1 contains many repeats of file numbers and Vlookup only returns the first value. In the event that there are multiple matches, I would like the macro to insert a row below the corresponding file number on sheet2 and copy the data from the second match there (and so on and so forth for multiple matches).


Sheet1:

File number name phone number
1 Don 253-214-2111
2 ray 213-123-1234
3 Charles 654-456-1233
1 Nancy 456-456-4564


Sheet2:

File number name phone number
1
2
3


After running the macro sheet2 should look like:

File number name phone number
1 Don 253-214-2111
Nancy 456-456-4564
2 ray 213-123-1234
3 Charles 654-456-1233


I've been pulling my hair out with this for a few days. any suggestions?

 

[Useful]

Hello and welcome!

Copy sheet1 data and paste it to sheet2. And sort them by numbers (A-Z). In result you will get the list that you want but if you want to get numbers without dublicates as showen above then try to use the below formula:
(for the cell A1)
=IF(COUNTIF($B$1:B1,B1)=1,B1,"")
considering that your number are in the B column