Highlight Unique Row - Based on First Three Columns Only

[elfmajesty]

Good afternoon, all. I have what I thought would be an easy thing to do, but I am having some troubles. I've researched and read many threads here to see if I could find it before posting here, but I've not had any luck. I'm on Excel 2010, too.

In my spreadsheet all records are unique rows due to Column E. However, I need to simply highlight any rows that are unique based on only the first three columns of the sheet.
I don't want anything removed or sorted, or changed in any way just highlighted. It will be a bit difficult to explain without being able to post a picture, but I'll do my best. I have this:

Number Yes/No Code Location CityCode
1 12 Yes NR LocationA 5896
2 12 Yes NR LocationC 2245
3 15 Yes NR LocationA 6879
4 15 Yes NR LocationC 1143
5 28 No NR LocationA 3456
6 28 Yes NR LocationC 7567
7 67 Yes NR LocationA 1245
8 67 Yes NR LocationC 9876
9 13 Yes PZ LocationA 8907
10 13 Yes NR LocationC 5576
11 95 Yes NR LocationA 3786
12 95 No NR LocationC 4679
13 68 Yes NR LocationA 1009
14 68 Yes NR LocationC 1675



I need to highlight the "unique" rows only looking at columns A, B and C. Thus, rows 5 and 6 would be "unique", rows 9 and 10 would be "unique" and rows 11 and 12 would be "unique".

It's a different situation than just simply using the conditional formatting to highlight unique values.

Any advice would be appreciated!

Thanks very much in advance,
LBurr

 

[Marcelo Branco]

Try this formula in Conditional Formatting and see if this OK

=IF(COUNTIF($A$2:$A$1000,$A2)>1,IF(ISNUMBER(MATCH($A2,IF(COUNTIFS($A$2:$A$1000,$A$2:$A$1000,$B$2:$B$1000,$B$2:$B$1000,$C$2:$C$1000,$C$2:$C$1000)=1,$A$2:$A$1000),0)),TRUE))

Notes:
1. It's an array formula. Thus if you use it in the worksheet, for testing purposes, you need to confirm with Ctrl+Shift+Enter. Entering in Conditional Formatting as a rule there is no need of Ctrl+Shift+Enter.

2. I set the last row in ranges = row 1000. This is arbitrary, so you can adjust to your case.

M.

 

[Marcelo Branco]

Try this new version in CF (a little bit shorter)

=AND(COUNTIF($A$2:$A$1000,$A2)>1,ISNUMBER(MATCH($A2,IF(COUNTIFS($A$2:$A$1000,$A$2:$A$1000,$B$2:$B$1000,$B$2:$B$1000,$C$2:$C$1000,$C$2:$C$1000)=1,$A$2:$A$1000),0)))

M.