Help with a formula comparing to sets of not matching data.

Copper Contributor

I have 2 sets of data coming from different sources.  I need to match them together, but they have not been entered in an identical fashion.

 

I am trying to compare two lists of names to each other and return a value if I can find a match that is 100% or less. For example, if I can match the name in column A with one in Column C, I return the name and ID of the value in C to the matching line in column A.  

 

I would like to do the same thing where there isn't a 100% match so I can match the two different sources to find other related lines without having to review them manually.

I would also like to show the % of relatedness if possible that the calculation to compare was able to find so I can focus on the ones that have the highest %.

 

Any assistance would be appreciated.

 

2 Replies

@kdove 

Hi 

One of the methods to compare records between 2 lists and return one of 4Options

  1. Records in List A not in List B
  2. records in List B not in List A
  3. Records existing in Both lists
  4. All the records on Both Lists

 

The easiest way is to create Merge Queries using the 2 lists in Power Query and change the join type  between the 2 lists.

Extracting a value accordingly is a straight forward Lookup Function or Index Function

 

Hope that helps

Nabil Mourad

@kdove 

Take a look at this thread at MrExcel Forum.

The latest version ´could be at #307 but there are more codes in later posts.