Conditional format Inv number

[gazmoz17]

Hi,

I know how to highlight duplicate inv numbers however, most of my cell contents in column A are repeated inv numbers so its not very helpful. Is there a way that:

1. None duplicates/repeated remain unformatted
2. Repeating Inv numbers have there own colour relevant to that invoice number
3. Or at least repeating invoice number have alternating 2 colour so I know when the invoice number has changed.

Open to VBA solution if required.

Overall as long as theres a colour break/change of colour when repeating inv numbers in col A stop repeating. eg alternating colour change blue and yellow when repeating inv numbers change and no fill for none duplicates.

*so A20 inv no 22541 no conditional format as not a duplicated inv number. Overall report is an Inv item report.

Many Thanks
Gareth

 

[Peter_SSs]

Would this be sufficient?

23 05 03.xlsm
	A
1
2	2
3	2
4	2
5	3
6	6
7	6
8	9
9	9
10	1
11	10
12	8
13	8
14	4
15	4
16	4
CF Inv No

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:A16	Expression	=AND(ISEVEN(ROWS(UNIQUE(A$2:A2))),COUNTIF(A$2:A$1000,A2)>1)	text	NO
A2:A16	Expression	=AND(ISODD(ROWS(UNIQUE(A$2:A2))),COUNTIF(A$2:A$1000,A2)>1)	text	NO

 

[Georgiboy]

It does not take into account the ones that are not duplicated but it would make it easier to see the changes, especially if the INV numbers are not ordered in series.

Book1
	A	B	C
2	22548		22541
3	22548		22548
4	22548		22548
5	22548		22548
6	22547		22548
7	22547		22542
8	22547		22542
9	22547		22539
10	22547		22539
11	22546		22539
12	22546		22539
13	22545		22545
14	22545		22545
15	22543		22543
16	22543		22543
17	22543		22543
18	22542		22547
19	22542		22547
20	22541		22547
21	22539		22547
22	22539		22547
23	22539		22546
24	22539		22546
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C2:C24	Other Type	Color scale		NO
A2:A24	Other Type	Color scale		NO

May not be suitable if you have 10,000's of INV numbers

 

[gazmoz17]

Would this be sufficient?

23 05 03.xlsm
	A
1
2	2
3	2
4	2
5	3
6	6
7	6
8	9
9	9
10	1
11	10
12	8
13	8
14	4
15	4
16	4
CF Inv No

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:A16	Expression	=AND(ISEVEN(ROWS(UNIQUE(A$2:A2))),COUNTIF(A$2:A$1000,A2)>1)	text	NO
A2:A16	Expression	=AND(ISODD(ROWS(UNIQUE(A$2:A2))),COUNTIF(A$2:A$1000,A2)>1)	text	NO

Hi Yeah works perfect thanks very much, much appreciated

 

[gazmoz17]

Thanks Georgiboy I can definitely use this on a smaller selection of invs

 

[Peter_SSs]

Hi Yeah works perfect thanks very much, much appreciated

You're welcome. Thanks for the follow-up.