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

 

[elfmajesty]

Okay - just found the download for the HTML code to post actual example - saw everyone else doing it! : ) (Very nice, by the way.) Here is a look of my example better:

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

 

[HalfAce]

Here's an idea that uses one extra column (anywhere you like), and conditional formatting. In this example I used column Z.
With row 1 being your first row of data, in Z1 enter the formula:

=A1&B1&C1

and copy down as far as your data goes. (I assumed row to 1000.)

Then, in cell A1, use conditional formatting with the formula:

=COUNTIF($Z$1:$Z$1000,$Z1)=1

(changing the 1000 to your real last row with data) and use Format painter to copy the (conditional) format of A1 and 'paste' it to all rows of data in column A - or columns A, B & C if you like.

Hope it helps.

 

[Marcelo Branco]

Select A2:F? till the end of the data (A2:F15 in your data sample) and

Conditional Formatting > New Rule > Use a formula to determine which cells to format

enter this Formula (Excel 2007 or higher)
=COUNTIFS($A:$A,$A2,$B:$B,$B2,$C:$C,$C2)=1

pick the format you want

M.

 

[elfmajesty]

Marcelo and HalfAce - both of these worked perfectly! Exactly what I needed. Thank you both very much for helping me think outside the box today.

Cheers to you,
elf

 

[elfmajesty]

Well, unfortunately, I think I've spoken too soon on this. I might need something different. It appears the formula will only work if there is only exactly TWO records to check againt each other, no more and no less. Marcello, I used yours for Conditional Formatting to make them BOLD/RED font. I also have put it in a forumla in another column as this:
=IF(COUNTIFS($A:$A,$A2,$B:$B,$B2,$C:$C,$C2)=1,"Conflict","OK")
to show it a bit better.

For multiples, I need to show the whole grouping as highlighted somehow. If you look at rows 12, 13 and 14 - I need them all to highlight to show that in the 95 group, the data is not all the same in columns A, B and C (even though 13 and 14 are).

For completely unique entries (with no grouping from column A), I do not want those to highlight.

I'm still playing around with some things, but thought I'd throw this back out there.

Thanks in advance,
elf

Excel Workbook
	A	B	C	D	E	F	G
1	Number	Yes/No	Code	Location	CityCode	USING FORMULA	*
2	12	Yes	NR	LocationA	5896	OK	*
3	12	Yes	NR	LocationC	2245	OK	*
4	15	Yes	NR	LocationA	6879	OK	*
5	15	Yes	NR	LocationC	1143	OK	*
6	28	No	NR	LocationA	3456	Conflict	*
7	28	Yes	NR	LocationC	7567	Conflict	*
8	67	Yes	NR	LocationA	1245	OK	*
9	67	Yes	NR	LocationC	9876	OK	*
10	13	Yes	PZ	LocationA	8907	Conflict	*
11	13	Yes	NR	LocationC	5576	Conflict	*
12	95	Yes	NR	LocationA	3786	Conflict	*
13	95	No	NR	LocationC	4679	OK	NEED THIS TO BE CONFLICT, TOO
14	95	No	NR	LocationE	6675	OK	NEED THIS TO BE CONFLICT, TOO
15	68	Yes	NR	LocationA	1009	OK	*
16	68	Yes	NR	LocationC	1675	OK	*
17	78	No	NR	LocationA	9967	OK	*
18	78	No	NR	LocationC	1453	OK	*
19	76	Yes	NR	LocationA	2756	Conflict	THIS SHOULD NOT BE A CONFLICT
20	28	Yes	PZ	LocationA	9789	Conflict	THIS SHOULD NOT BE A CONFLICT
Sheet4

the

 

[elfmajesty]

Let's try this one instead, since I didn't realize that I had a grouping of "28" which made row 20 on the previous example a bit confusing.

Excel Workbook
	A	B	C	D	E	F	G
1	Number	Yes/No	Code	Location	CityCode	USING FORMULA	*
2	12	Yes	NR	LocationA	5896	OK	*
3	12	Yes	NR	LocationC	2245	OK	*
4	15	Yes	NR	LocationA	6879	OK	*
5	15	Yes	NR	LocationC	1143	OK	*
6	28	No	NR	LocationA	3456	Conflict	*
7	28	Yes	NR	LocationC	7567	Conflict	*
8	67	Yes	NR	LocationA	1245	OK	*
9	67	Yes	NR	LocationC	9876	OK	*
10	13	Yes	PZ	LocationA	8907	Conflict	*
11	13	Yes	NR	LocationC	5576	Conflict	*
12	95	Yes	NR	LocationA	3786	Conflict	*
13	95	No	NR	LocationC	4679	OK	NEED THIS TO BE CONFLICT, TOO
14	95	No	NR	LocationE	6675	OK	NEED THIS TO BE CONFLICT, TOO
15	68	Yes	NR	LocationA	1009	Conflict	*
16	68	*	NR	LocationC	1675	OK	*
17	78	No	NR	LocationA	9967	OK	*
18	78	No	NR	LocationC	1453	OK	*
19	76	Yes	NR	LocationA	2756	Conflict	THIS SHOULD NOT BE A CONFLICT
20	45	Yes	PZ	LocationA	9789	Conflict	THIS SHOULD NOT BE A CONFLICT
21	*	*	*	*	*	*	*
Sheet4

 

[Marcelo Branco]

Hmm...i'm not following you...

My formula does exactly what you asked in your first post.

In my spreadsheet all records are unique rows due to Column E.However, I need to simply highlight any rows that are uniquebased on only the first three columns of the sheet.

Why rows 19 and 20 should not be highlighted? They are unique considering columns A, B and C.

And why rows 13 and 14 should? They are not unique.

I'm lost...

M.

 

[Marcelo Branco]

One question: is the value in row 16 column B "*" correct? Or is it a typo? Shouldn't it be Yes or No?

M.

 

[elfmajesty]

Hi, Marcelo. 16 B was a typo, yes. Your formula is great and does work like it should. However, upon utilizing, we realized it wasn't quite what we needed once we had it implemented on a larger set of data.

I can see where your confusion is and that's why I'm thinking I might need something slightly different than simply highlighting unique rows. : )

Rows 12, 13 and 14 are the best example here. What we were trying to show is where there are records that are within the same Number group (i.e. 95) that are not EXACTLY the same across all of them in the "Yes/No" and the "Code" within the group. Thus BOTH rows 13 and row 14 conflict with row 12 and we'd like to highlight the entire group as having a conflict.

Also, because the formula is designed to highlight unique, any record that is the only one in a "Number group" (i.e. rows 19 and 20) does highlight the way it should. However, there is nothing to "conflict" with, so we do not really need to show those.

Simply put, we are looking to find inconistencies within records that have more than one in the Number group.

I'm still working on a few thoughts about it and think we may have a solution. However, any thoughts from you would be appreciated as well.

Thanks for taking the time.

Cheers,
elf