Simple colour coding of column data

[Bebbspoke]

Hi Peoples - plz - Conditional formatting of column (text) data req: -

if A1 = A2 then A2 is same fill colour as A1... strings down data column...
&
if A1 <> A2 then A2 is different colour to A1... strings down data column...

Seems highly useful for quick visual reference change of similar data blocks; - but darned if I can suss out the Conditional Formatting syntax!!!

Thank you,
Bebbspoke

 

[PA HS Teacher]

It's been half a decade since I've posted here. But if I'm understanding you problem, can you just use conditional formatting in A2. Custom Formula: =(A1=A2)

Then use regular formatting in A1, but not in A2?

 

[Bebbspoke]

Thanks PA - I'll give yuor suggecestion a try & post back...

 

[Peter_SSs]

If I have understood correctly, here is one way that keeps the CF syntax very simple.

1. Put a 1 (or -1) in F1, or row 1 of any available column.
2. Put the formula shown in row 2 of that column & copy down as far as you might ever need (after adjusting the $1000 to be greater than the number of rows of data you will ever have)
3. Select A2:F?? (I've used A2:F20, being where my column F formulas end) and apply the Conditional Formatting shown.
4. Column F can now be hidden if you want.

Excel Workbook
	A	B	C	D	E	F
1	User ID	Other 1	Other 2	Other 3	Other 4	1
2	a					-1
3	a					-1
4	b					1
5	b					1
6	b					1
7	b					1
8	c					-1
9	c					-1
10	c					-1
11	d					1
12	e					-1
13						1
14						1
15	f					-1
16	f					-1
17	g					1
18						0
19						0
20						0
Cond Format Groups

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
F2	1. / Formula is =$F2=1	Abc

 

[Bebbspoke]

Thanks Peter - I'll give your suggestion a try... PA's suggestion did not quite "behave" at the end of each block...
Your example shows both consistent behaviour and ability to remain true when blanks occur in the data.
I was hoping to avoid creating further rows - as there is much data & processing already & eventually I wish to make a spreadsheet to import block data from other sources... anyways... no sweat really with another column.. it's just nice & tidy were it one conditional rule for the one column...
Cheers & thanks again,
Bebbspoke

 

[Peter_SSs]

I was hoping to avoid creating further rows ..

I assume you mean columns, not rows.

This can be done without the helper column, but it somewhat defeats you other consideration ..

... as there is much data & processing already

.. as there is probably more processing this way, especially since Conditional Formatting is volatile

For this one, select A3:D?? and apply the CF shown.

Excel Workbook
	A	B	C	D
1	Hdr 1	Hdr 2	Hdr 3	Hdr 4
2	A
3	A
4	A
5	B
6	B
7	C
8	C
9	C
10
11	D
12	D
13	E
14
15
Cond Format In Groups

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A3	1. / Formula is =IF(COUNTA($A3:$A$1000),MOD(SUMPRODUCT(--($A$2:$A2<>$A$3:$A3)),2),0)	Abc

 

[Bebbspoke]

Peter; - many thanks for your further suggestion & comment.

Cheers, Bebbspoke.

 

[Peter_SSs]

Peter; - many thanks for your further suggestion & comment.

Cheers, Bebbspoke.

No problem. Glad to help.