1

I have two sets of data, list A and list B.

List A is defined as the master list. Everything on this list must be accounted for in the final match. Let's say it has 500 rows of data like DBA, address, city, country, etc. List B is 6,000 rows with similar information.

The first issue is there may be multiple matches in List B that connect back to List A. They all need to be captured in the final match output with information like DBA, address, ID #, etc. so probably formatted in rows.

The second issue is there may not be a clear common term for both of them. Addresses can be spelled in so many different ways, plus it's often in foreign languages. Same with DBA, cities, etc.

The third issue is there's also no guarantee there's a match on List B for all the items on List A.

Thoughts on how I can approach this? I've been doing it manually for a while. I'll take part of the DBA or address in List A and just do a Ctrl+F in List B. If there are more than one match, I'll add a row in List A and put all the matches from List B there. As you can imagine, this is excrutiating and I'd love a more efficient way to do this. Thank you in advance for your help!

user3081053
  • 11
  • 1

1 Answers1

0

The first step would be to create a common key for each list.

You could use formulas like "find" to search for sub strings of addresses and create indicator column based on this. Put a few indicators together to make a unique key possibly with the persons name or something like that.

Once you have a unique key in each list you can use the excel vlookup function to match the lists.

Kharoof
  • 597
  • 1
  • 6
  • 21