Comparing lists

[Deleted]

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 · Accepted 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),””)

[#3]

@Deleted 

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