highlight duplicate values, but with a different color for each unique value

[smking204]

Title should be pretty self-explanatory. Seems like something I could figure out, I just don't have the time...

So, like, if my list is: 1, 2, 3, 3, 4, 5, 6, 7, 7, 7, 8, 9, 9, I want the 3's highlighted one color, the 7's a different color, and the 9's a third color.

I'm so demanding, I know.

Thanks in advance, y'all!

 

[dreid1011]

Title should be pretty self-explanatory. Seems like something I could figure out, I just don't have the time...

So, like, if my list is: 1, 2, 3, 3, 4, 5, 6, 7, 7, 7, 8, 9, 9, I want the 3's highlighted one color, the 7's a different color, and the 9's a third color.

I'm so demanding, I know.

Thanks in advance, y'all!

How many possible numbers can there be? For CF, you will have to have a separate rule for each number. VBA might be a better option if there are many possible numbers, but I can't think of it off the top of my head.

 

[smking204]

How many possible numbers can there be? For CF, you will have to have a separate rule for each number. VBA might be a better option if there are many possible numbers, but I can't think of it off the top of my head.

thanks, yes, CF should be fine. 7-10 iterations of a rule should be sufficient.

 

[dreid1011]

Okay, well if this looks like what you want, then duplicate one of the rules for each other number you could possibly have:

Book1
	A
1	1
2	2
3	3
4	3
5	4
6	5
7	6
8	7
9	7
10	7
11	8
12	9
13	9
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A1:A13	Expression	=AND($A1=9,COUNTIF($A$1:$A1,9)>1)	text	NO
A1:A13	Expression	=AND($A1=7,COUNTIF($A$1:$A1,7)>1)	text	NO
A1:A13	Expression	=AND($A1=3,COUNTIF($A$1:$A1,3)>1)	text	NO

 

[smking204]

Okay, well if this looks like what you want, then duplicate one of the rules for each other number you could possibly have:

Book1
	A
1	1
2	2
3	3
4	3
5	4
6	5
7	6
8	7
9	7
10	7
11	8
12	9
13	9
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A1:A13	Expression	=AND($A1=9,COUNTIF($A$1:$A1,9)>1)	text	NO
A1:A13	Expression	=AND($A1=7,COUNTIF($A$1:$A1,7)>1)	text	NO
A1:A13	Expression	=AND($A1=3,COUNTIF($A$1:$A1,3)>1)	text	NO

hmmm ok. Let me tighten up my question. The values in the original list could be anything (usually a 4 digit number). The list will only be about 20 items or so at most, and I will only expect 7-10 of those, at most, to have duplicates.

I'm aiming for the formula to be like, "if any values appear more than once, filter out a sublist of those values, then assign a format to the nth value (using MATCH??) in that sublist." I could create 7 different formulas for n. Does that make sense? I poked at it, and just don't have the time right now...

 

[dreid1011]

Okay, maybe something like this then. Assuming your IDs/numbers are in col A, use the formula in col C to identify the duplicates in col A.

Cols D:E are just for reference on how the CF rules are working. Each row of the duplicate ID col is what determines the color based on what # MATCH returns.

Book1
	A	B	C	D	E
1	List of IDs		Duplicate IDs	Color Legend	ID to Duplicate Match
2	17		12	1	4
3	13		13	2	2
4	13		16	3	2
5	19		17	4	6
6	12		18	5	1
7	11		19	6	#N/A
8	18			7	5
9	14			8	#N/A
10	18			9	5
11	16			10	3
12	19				6
13	13				2
14	12				1
15	16				3
16	17				4
17	17				4
18	12				1
19	19				6
20	19				6
21	10				#N/A
Sheet1

Cell Formulas
Range	Formula
C2:C7	C2	=SORT(UNIQUE(FILTER(A2:A21,COUNTIF(A2:A21,A2:A21)>1)))
E2:E21	E2	=MATCH(A2,$C$2#,0)
Dynamic array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:A21	Expression	=MATCH(A2,$C$2#,0)=10	text	NO
A2:A21	Expression	=MATCH(A2,$C$2#,0)=9	text	NO
A2:A21	Expression	=MATCH(A2,$C$2#,0)=8	text	NO
A2:A21	Expression	=MATCH(A2,$C$2#,0)=7	text	NO
A2:A21	Expression	=MATCH(A2,$C$2#,0)=6	text	NO
A2:A21	Expression	=MATCH(A2,$C$2#,0)=5	text	NO
A2:A21	Expression	=MATCH(A2,$C$2#,0)=4	text	NO
A2:A21	Expression	=MATCH(A2,$C$2#,0)=3	text	NO
A2:A21	Expression	=MATCH(A2,$C$2#,0)=2	text	NO
A2:A21	Expression	=MATCH(A2,$C$2#,0)=1	text	NO

I will adjust the CF formula if you only want a specific duplicate to be highlighted. IE: all duplicates except the first unique instance of each number.

 

[smking204]

Yes, this seems to give me what I need! I embedded the SORT within the MATCH, and set it equal to n, like this:

=MATCH(A2,SORT(UNIQUE(FILTER($A$2:$A$35,COUNTIF($A$2:$A$35,$A$2:$A$35)>1))),0)=1

I wasn't sure how to use the COUNTIF in that way, I'll have to study that later.

Curious if the SORT is unnecessary here...?

And can you point me to a resource on what the # is doing in your MATCH formula? I've never seen that. Is that like a "length of" or something?

Thanks a bunch!

 

[dreid1011]

Yes, the SORT is unnecessary included in the CF formula like that. I was using it for readability on the sheet. And the # is referring to the spilled range of the formula: '=SORT(UNIQUE(FILTER(A2:A21,COUNTIF(A2:A21,A2:A21)>1)))'

You're welcome, and happy to help.