Highlight row for every first unique value

[Kennethc]

Hi
In column C on a large sheet, I have ID numbers in a format similar to AZO01B000034-10296-AZO01AMC0011-31008.
The same ID will be listed across a number of rows, and then very few rows the ID will change, e.g.

Column C
AZO01B000034-10296-AZO01AMC0011-31008
AZO01B000034-10296-AZO01AMC0011-31008
AZO01B000034-10296-AZO01AMC0011-31008
AZO01B000034-10296-AZO01AMC0011-30291
AZO01B000034-10296-AZO01AMC0011-30291
AZO01B000034-10296-AZO01AMC0011-29625
AZO01B000034-10296-AZO01AMC0011-29625
AZO01B000034-10296-AZO01AMC0011-29625
AZO01B000034-10296-AZO01AMC0011-29625
AZO01B000034-10296-AZO01AMC0011-29601
AZO01B000034-10296-AZO01AMC0011-29601
AZO01B000034-10296-AZO01AMC0011-29601

How do I format the entire row that has the first instance of each unique ID number?

Many thanks.

[AliGW]

HOW TO ATTACH YOUR SAMPLE WORKBOOK: Fast answers need clear examples. Post a small Excel sheet (not a picture) showing realistic & representative sample data WITHOUT confidential information (10-20 rows, not thousands) and some manually calculated results. For a new thread (1st post), scroll to Manage Attachments, otherwise scroll down to GO ADVANCED, click, and then scroll down to MANAGE ATTACHMENTS and click again. Now follow the instructions at the top of that screen. Screenshots are of little practical use as we cannot manipulate them.

A good sample workbook has just 10-20 rows of representative data that has been desensitised. The sample layout accurately matches that of your real data. It also has expected results mocked up, worked examples where required, relevant cells highlighted and a few explanatory notes.

[cysu11]

Hi Kenneth,

format condition, and the formula is: =COUNTIF($C$1:C1;C1)=1

BR, Alexandra

[6StringJazzer]

You did not mention conditional formatting, but I'm going to assume you know how it works. If not, let me know and I'll give you step-by-step.

Use this as your conditional formatting rule. You say "the entire row" but I am sure you are not using 16,000 columns. The Applies To range is $A:$Z, where Z is the last column you are actually using. Change to suit.

Please Login or Register  to view this content.

[Roel Jongman]

Since the data was in the srartpost I copied it to an example file for you.

What you want is easy to achieve with a conditional format formula
with this formula:

Please Login or Register  to view this content.

the key is to expand the count range for every row and only fix the topleft cel with $

[AliGW]

CF rule for C1:

=COUNTIF($C$1:$C1,$C1)=1

Applies to: $C:$C

And it's a COLUMN, not a row.

[6StringJazzer]

Please note:

format condition, and the formula is: =COUNTIF($C$1:C1;C1)=1

Please Login or Register  to view this content.

CF rule for C1:

=COUNTIF($C$1:$C1,$C1)=1

Applies to: $C:$C

OP requested to "format the entire row."

[AliGW]

Just needs to change the applies to range accordingly, although I see no benefit in highlighting hundreds of columns.

This should de:

Applies to: $A:$ZZ

[6StringJazzer]

Here is my formula applied to a mock-up, with an additional condition to exclude a header row. OP did not show a header row, but there almost always is one.

	A	B	C	D
1	Some Data	Some more Data	Some String	Some Other Data
2	6	36	AZO01B000034-10296-AZO01AMC0011-31008	44
3	36	80	AZO01B000034-10296-AZO01AMC0011-31008	4
4	72	48	AZO01B000034-10296-AZO01AMC0011-31008	23
5	96	93	AZO01B000034-10296-AZO01AMC0011-30291	70
6	100	45	AZO01B000034-10296-AZO01AMC0011-30291	72
7	18	97	AZO01B000034-10296-AZO01AMC0011-29625	39
8	62	30	AZO01B000034-10296-AZO01AMC0011-29625	37
9	96	11	AZO01B000034-10296-AZO01AMC0011-29625	29
10	38	5	AZO01B000034-10296-AZO01AMC0011-29625	91
11	97	62	AZO01B000034-10296-AZO01AMC0011-29601	37
12	76	71	AZO01B000034-10296-AZO01AMC0011-29601	77
13	84	47	AZO01B000034-10296-AZO01AMC0011-29601	32

Conditional Formatting Rules

Formula
Applies To	Formula	Format	Stop If True
$A:$D	=AND(ROW(A1)>1,COUNTIF($C$1:$C1,$C1)=1)		False