• 669K Members
• 9,633 Online
• 825K Conversations
SOLVED

Highlighted
Deleted
Not applicable

# Comparing lists

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

# Re: Comparing lists

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

# Re: Comparing lists

@Deleted

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