SOLVED
Home

Comparing lists

%3CLINGO-SUB%20id%3D%22lingo-sub-622812%22%20slang%3D%22en-US%22%3EComparing%20lists%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-622812%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20...%3C%2FP%3E%3CP%3EWould%20appreciate%20help%20with%20the%20following%20query.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20two%20lists%20of%20names%20as%20follows%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%26nbsp%3B%20List%20A%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20List%20B%3C%2FP%3E%3CP%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%26nbsp%3B%20Smith%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20Smith%3C%2FP%3E%3CP%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%26nbsp%3B%20Williams%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20Williams%3C%2FP%3E%3CP%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%26nbsp%3B%20Jones%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%26nbsp%3B%20Jones%3C%2FP%3E%3CP%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%26nbsp%3B%20Jones%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%26nbsp%3B%20Evans%3C%2FP%3E%3CP%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%26nbsp%3B%20Evans%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%26nbsp%3B%20Lewis%3C%2FP%3E%3CP%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%26nbsp%3B%20Lewis%3C%2FP%3E%3CP%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%26nbsp%3B%20Williams%3C%2FP%3E%3CP%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%26nbsp%3B%20Jones%3C%2FP%3E%3CP%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%26nbsp%3B%20Evans%3C%2FP%3E%3CP%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%26nbsp%3B%20Smith%3C%2FP%3E%3CP%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%26nbsp%3B%20Morgan%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EList%20A%20contains%20a%20number%20of%20duplications%20that%20I%20want%20to%20refine%20into%20a%20unique%20list%20(List%20B).%20Is%20it%20possible%2C%20therefore%2C%20to%20create%20a%20formula%20that%20will%20search%20List%20A%20to%20identify%20if%20a)%20the%20names%20already%20exists%20in%20List%20B%20and%20if%20not%20b)%20will%20add%20the%20new%20name%20(for%20example%2C%20Morgan)%20to%20List%20B.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI've%20been%20compiling%20List%20B%20manually%20up%20to%20now%20but%20List%20A%20is%20becoming%20so%20extensive%20that%20errors%20are%20being%20to%20creep%20in.%26nbsp%3B%20Any%20help%20would%20therefore%20be%20gratefully%20appreciated.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EJames%20Clancy%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-622812%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-623155%22%20slang%3D%22en-US%22%3ERe%3A%20Comparing%20lists%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-623155%22%20slang%3D%22en-US%22%3ETo%20create%20a%20distinct%20sorted%20list%2C%20write%20this%20formula%20in%20B2%20and%20copy%20it%20down%3A%3CBR%20%2F%3E%3DIFERROR(LOOKUP(2%2C%3CBR%20%2F%3E1%2F(COUNTIF(A%242%3AA%24100%2C%E2%80%9D%26gt%3B%3D%E2%80%9C%26amp%3BA%242%3AA%24100)%3DMAX(INDEX(COUNTIF(A%242%3AA%24100%2C%E2%80%9D%26gt%3B%3D%E2%80%9C%26amp%3BA%242%3AA%24100)*(COUNTIF(B%241%3AB1%2CA%242%3AA%24100)%3D0)%2C0)))%2C%3CBR%20%2F%3EA%242%3AA%24100)%2C%E2%80%9D%E2%80%9D)%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-623167%22%20slang%3D%22en-US%22%3ERe%3A%20Comparing%20lists%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-623167%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F292929%22%20target%3D%22_blank%22%3E%40Iriwel%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%20-%20have%20you%20tried%20'Data'%20'%20Remove%20Duplicates'%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Deleted
Not applicable

Hi ...

Would appreciate help with the following query.

 

I have two lists of names as follows:

 

       List A                                           List B

       Smith                                           Smith

       Williams                                       Williams

       Jones                                            Jones

       Jones                                            Evans

       Evans                                            Lewis

       Lewis

       Williams

       Jones

       Evans

       Smith

       Morgan

 

List A contains a number of duplications that I want to refine into a unique list (List B). Is it possible, therefore, to create a formula that will search List A to identify if a) the names already exists in List B and if not b) will add the new name (for example, Morgan) to List B.

 

I've been compiling List B manually up to now but List A is becoming so extensive that errors are being to creep in.  Any help would therefore be gratefully appreciated.

 

James Clancy

2 Replies
Highlighted
Solution
To create a distinct sorted list, write this formula in B2 and copy it down:
=IFERROR(LOOKUP(2,
1/(COUNTIF(A$2:A$100,”>=“&A$2:A$100)=MAX(INDEX(COUNTIF(A$2:A$100,”>=“&A$2:A$100)*(COUNTIF(B$1:B1,A$2:A$100)=0),0))),
A$2:A$100),””)
Highlighted

@Deleted 

 

Hi - have you tried 'Data' ' Remove Duplicates'?