Hi, I have 2 spreadsheets that I have to merge in order to compare data. Spreadsheet 1 has member details for around 100 members. Each member has an NI number to identify them. Each member on this list appears only once.
On spreadsheet 2, there are around 100,000 records. I need to pull the same members details into spreadsheet 1. Problem is, each member in this spreadsheet can appear multiple times. How can I pull in entire rows based on NI number? Vlookups only pull in the first instance, I want to bring in ALL instances. Can anybody help?
Edit: To add more information;
Apparently, the way that it has been done before at my workplace was to use the NI number and concatenate with a date so you end up with unique identifiers that look like AB123452014 (where NI was AB12345 and date of that particular record was 2014). This was done on both spreadsheets; the one with 100 records as well as the one with 100,000.
I also created records that have just the NI. So AB12345. Lets say this NI refers to A Smith. On the large spreadsheet, there are three records relating to him. These are dating 1997, 2012 and 2014. So the ID looks like AB123451997, AB123452012, and AB123452014.
On the smaller spreadsheet, I want to draw in all records relating to AB12345. Can I use a vlookup to find and retrieve all records (rows) that begin with this?
On spreadsheet 2, there are around 100,000 records. I need to pull the same members details into spreadsheet 1. Problem is, each member in this spreadsheet can appear multiple times. How can I pull in entire rows based on NI number? Vlookups only pull in the first instance, I want to bring in ALL instances. Can anybody help?
Edit: To add more information;
Apparently, the way that it has been done before at my workplace was to use the NI number and concatenate with a date so you end up with unique identifiers that look like AB123452014 (where NI was AB12345 and date of that particular record was 2014). This was done on both spreadsheets; the one with 100 records as well as the one with 100,000.
I also created records that have just the NI. So AB12345. Lets say this NI refers to A Smith. On the large spreadsheet, there are three records relating to him. These are dating 1997, 2012 and 2014. So the ID looks like AB123451997, AB123452012, and AB123452014.
On the smaller spreadsheet, I want to draw in all records relating to AB12345. Can I use a vlookup to find and retrieve all records (rows) that begin with this?
Last edited:
