# compare data in two columns - displaying only unique items.

#### excelbeginner1

##### New Member
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

#### Attachments

• Capture.JPG
15.6 KB · Views: 3

### Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

#### GraH

##### Well-known Member
Hi

There might be better formula available, but this one might just do (or use a conditional format)
Book1
ABCDEF
1List 1List 2List 2 Filtered
2PeterJamesJames
3MaryJohnPaul
4JohnPaulTed
5TedSusan
6Susan
7
8
9
10
11
12
13
14
15
16
17
18
19
20
Sheet1
Cell Formulas
RangeFormula
E2:E14E2=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:E17E15=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
CellConditionCell FormatStop If True
C1:C20Expression=ISNUMBER(MATCH(\$C1;\$A:\$A;0))textNO

Or alternative, prevent a duplicate to be entered

Book1
ABCD
1List 1List 2
2PeterJames
3MaryPaul
4JohnTed
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
Sheet1
Cells with Conditional Formatting
CellConditionCell FormatStop If True
C1Expression=ISNUMBER(MATCH(\$C1;\$A:\$A;0))textNO
Cells with Data Validation
CellAllowCriteria
C2:C20Custom=ISERROR(MATCH(\$C2;\$A:\$A;0))

Last edited:

Replies
1
Views
29
Replies
1
Views
42
Replies
3
Views
121
Replies
7
Views
199
Replies
7
Views
158