compare data in two columns - displaying only unique items.

[excelbeginner1]

Good day
please could you assist me, not sure if it is possible.

I have two columns that return the top three results from a list, i would like to know if there is any way that i could compare the two lists and if the is a duplicate value/result in list b then this
would be removed and the next unique result will be displayed.

Cloumn A must remain unchanged. only column B changes. Clolumns are being populated by coping results.

In the example John appears in a &b , i need to remove it from B so that Paul would be the nexy name

thnk you in advance

 

[GraH]

Hi

There might be better formula available, but this one might just do (or use a conditional format)

Book1
	A	B	C	D	E	F
1	List 1		List 2		List 2 Filtered
2	Peter		James		James
3	Mary		John		Paul
4	John		Paul		Ted
5			Ted		Susan
6			Susan
7
8
9
10
11
12
13
14
15
16
17
18
19
20
Sheet1

Cell Formulas
Range	Formula
E2:E14	E2	=IFERROR(INDEX($C$2:$C$6,AGGREGATE(15,6,(ROW($C$2:$C$6)-1)/((COUNTIF($A$2:$A$4,$C$2:$C$6)+1)=1),ROW()-1)),"")
E15:E17	E15	=IF((ROW()-1)>COUNTA($C$2:$C$6),"",INDEX($C$2:$C$6,AGGREGATE(15,6,(ROW($C$2:$C$6)-1)/((COUNTIF($A$2:$A$4,$C$2:$C$6)+1)=1),ROW()-1)))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C1:C20	Expression	=ISNUMBER(MATCH($C1;$A:$A;0))	text	NO

Or alternative, prevent a duplicate to be entered

Book1
	A	B	C	D
1	List 1		List 2
2	Peter		James
3	Mary		Paul
4	John		Ted
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C1	Expression	=ISNUMBER(MATCH($C1;$A:$A;0))	text	NO

Cells with Data Validation
Cell	Allow	Criteria
C2:C20	Custom	=ISERROR(MATCH($C2;$A:$A;0))