Comparing lists

Question

Hi ...Would appreciate help with the following query.&nbsp;I have two lists of names as follows:&nbsp;&nbsp; &nbsp; &nbsp;&nbsp; List A &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; List B&nbsp; &nbsp; &nbsp;&nbsp; Smith &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Smith&nbsp; &nbsp; &nbsp;&nbsp; Williams &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Williams&nbsp; &nbsp; &nbsp;&nbsp; Jones &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; Jones&nbsp; &nbsp; &nbsp;&nbsp; Jones &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; Evans&nbsp; &nbsp; &nbsp;&nbsp; Evans &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; Lewis&nbsp; &nbsp; &nbsp;&nbsp; Lewis&nbsp; &nbsp; &nbsp;&nbsp; Williams&nbsp; &nbsp; &nbsp;&nbsp; Jones&nbsp; &nbsp; &nbsp;&nbsp; Evans&nbsp; &nbsp; &nbsp;&nbsp; Smith&nbsp; &nbsp; &nbsp;&nbsp; Morgan&nbsp;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.&nbsp;I've been compiling List B manually up to now but List A is becoming so extensive that errors are being to creep in.&nbsp; Any help would therefore be gratefully appreciated.&nbsp;James Clancy

twifoo · Accepted Answer

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

Forum Discussion

Comparing lists

2 Replies

Resources