Home

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

%3CLINGO-SUB%20id%3D%22lingo-sub-766403%22%20slang%3D%22en-US%22%3EHelp%20with%20a%20formula%20comparing%20to%20sets%20of%20not%20matching%20data.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-766403%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%202%20sets%20of%20data%20coming%20from%20different%20sources.%26nbsp%3B%20I%20need%20to%20match%20them%20together%2C%20but%20they%20have%20not%20been%20entered%20in%20an%20identical%20fashion.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20trying%20to%20compare%20two%20lists%20of%20names%20to%20each%20other%20and%20return%20a%20value%20if%20I%20can%20find%20a%20match%20that%20is%20100%25%20or%20less.%20For%20example%2C%20if%20I%20can%20match%20the%20name%20in%20column%20A%20with%20one%20in%20Column%20C%2C%20I%20return%20the%20name%20and%20ID%20of%20the%20value%20in%20C%20to%20the%20matching%20line%20in%20column%20A.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20would%20like%20to%20do%20the%20same%20thing%20where%20there%20isn't%20a%20100%25%20match%20so%20I%20can%20match%20the%20two%20different%20sources%20to%20find%20other%20related%20lines%20without%20having%20to%20review%20them%20manually.%3C%2FP%3E%3CP%3EI%20would%20also%20like%20to%20show%20the%20%25%20of%20relatedness%20if%20possible%20that%20the%20calculation%20to%20compare%20was%20able%20to%20find%20so%20I%20can%20focus%20on%20the%20ones%20that%20have%20the%20highest%20%25.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20assistance%20would%20be%20appreciated.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-766403%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-766431%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20a%20formula%20comparing%20to%20sets%20of%20not%20matching%20data.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-766431%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F379704%22%20target%3D%22_blank%22%3E%40kdove%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%26nbsp%3B%3C%2FP%3E%3CP%3EOne%20of%20the%20methods%20to%20compare%20records%20between%202%20lists%20and%20return%20one%20of%204Options%3C%2FP%3E%3COL%3E%3CLI%3ERecords%20in%20List%20A%20not%20in%20List%20B%3C%2FLI%3E%3CLI%3Erecords%20in%20List%20B%20not%20in%20List%20A%3C%2FLI%3E%3CLI%3ERecords%20existing%20in%20Both%20lists%3C%2FLI%3E%3CLI%3EAll%20the%20records%20on%20Both%20Lists%3C%2FLI%3E%3C%2FOL%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20easiest%20way%20is%20to%20create%20Merge%20Queries%20using%20the%202%20lists%20in%20Power%20Query%20and%20change%20the%20join%20type%26nbsp%3B%20between%20the%202%20lists.%3C%2FP%3E%3CP%3EExtracting%20a%20value%20accordingly%20is%20a%20straight%20forward%20Lookup%20Function%20or%20Index%20Function%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHope%20that%20helps%3C%2FP%3E%3CP%3ENabil%20Mourad%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-766440%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20a%20formula%20comparing%20to%20sets%20of%20not%20matching%20data.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-766440%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F379704%22%20target%3D%22_blank%22%3E%40kdove%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETake%20a%20look%20at%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fwww.mrexcel.com%2Fforum%2Fexcel-questions%2F195635-fuzzy-matching-new-version-plus-explanation.html%22%20target%3D%22_self%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ethis%20thread%3C%2FA%3E%26nbsp%3Bat%20MrExcel%20Forum.%3C%2FP%3E%3CP%3EThe%20latest%20version%20%C2%B4could%20be%20at%20%23307%20but%20there%20are%20more%20codes%20in%20later%20posts.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
kdove
Occasional Visitor

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.

 

Related Conversations
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
Edge insider Dev bypasses IE mode website list
HotCakeX in Enterprise on
4 Replies