Conditional formatting, perhaps?

[raeannb]

Hello! I have a table with a list of values, many of which repeat. It's a loooong table, and I'd really like to have the values alternate color, to make reading the table much easier. For example, if my column A contains:

A
A
A
B
B
B
B
B
C
C
D

I'd like the A and C rows to be blue (or whatever color), while B and D values have no fill. I can't predict what the entries will contain, nor do I know how many of each value will occur. Thoughts? I've looked into conditional formatting a bit, but I've never used it before so I'm inditimidated. Thank you for your help!!

 

[Jonmo1]

Try this..

If the values start in A2
Highlight A2 down to the end of the data

In conditional formatting use the formula
=MOD(SUMPRODUCT(1/COUNTIF(A$2:A2,A$2:A2)),2)=1
Apply your blue fill format

Hope that helps.

Excel Workbook
	A
1	Header
2	A
3	A
4	A
5	B
6	B
7	B
8	B
9	B
10	C
11	C
12	D
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2	1. / Formula is =MOD(SUMPRODUCT(1/COUNTIF(A$2:A2,A$2:A2)),2)=1	Abc
A3	1. / Formula is =MOD(SUMPRODUCT(1/COUNTIF(A$2:A3,A$2:A3)),2)=1	Abc
A4	1. / Formula is =MOD(SUMPRODUCT(1/COUNTIF(A$2:A4,A$2:A4)),2)=1	Abc
A5	1. / Formula is =MOD(SUMPRODUCT(1/COUNTIF(A$2:A5,A$2:A5)),2)=1	Abc
A6	1. / Formula is =MOD(SUMPRODUCT(1/COUNTIF(A$2:A6,A$2:A6)),2)=1	Abc
A7	1. / Formula is =MOD(SUMPRODUCT(1/COUNTIF(A$2:A7,A$2:A7)),2)=1	Abc
A8	1. / Formula is =MOD(SUMPRODUCT(1/COUNTIF(A$2:A8,A$2:A8)),2)=1	Abc
A9	1. / Formula is =MOD(SUMPRODUCT(1/COUNTIF(A$2:A9,A$2:A9)),2)=1	Abc
A10	1. / Formula is =MOD(SUMPRODUCT(1/COUNTIF(A$2:A10,A$2:A10)),2)=1	Abc
A11	1. / Formula is =MOD(SUMPRODUCT(1/COUNTIF(A$2:A11,A$2:A11)),2)=1	Abc
A12	1. / Formula is =MOD(SUMPRODUCT(1/COUNTIF(A$2:A12,A$2:A12)),2)=1	Abc

 

[Oaktree]

This will be easier if you add a helper column.

If your column of ABCD starts in B2, try:

A2 = 1
A3 =IF(B3=B2,A2,--NOT(A2))

Then, select rows 2:12 (or whatever the end of your range is) and go to format --> conditional formatting

Change "Cell value is" to "formula is" and enter =$A2=0 as your formula, click Format, click on the "Patterns" tab, click the color you'd want to apply, and click OK twice.

 

[Marcelo Branco]

Maybe using a helper column (C in the example below)

A B C
<TABLE style="WIDTH: 144pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=192><COLGROUP><COL style="WIDTH: 48pt" span=3 width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 48pt; FONT-FAMILY: Verdana; BACKGROUND: #c5d9f1; HEIGHT: 15pt; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" class=xl66 height=20 width=64>A


</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=64></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=64 align=right>1</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; FONT-FAMILY: Verdana; BACKGROUND: #c5d9f1; HEIGHT: 15pt; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" class=xl66 height=20>A</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>1</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; FONT-FAMILY: Verdana; BACKGROUND: #c5d9f1; HEIGHT: 15pt; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" class=xl66 height=20>A</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>1</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=20>B</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>0</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=20>B</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>0</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=20>B</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>0</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=20>B</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>0</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=20>B</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>0</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; FONT-FAMILY: Verdana; BACKGROUND: #c5d9f1; HEIGHT: 15pt; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" class=xl66 height=20>C</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>1</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; FONT-FAMILY: Verdana; BACKGROUND: #c5d9f1; HEIGHT: 15pt; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" class=xl66 height=20>C</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>1</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=20>D


</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" id=td_post_2836894 class=xl65></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>0</TD></TR></TBODY></TABLE>

in C1 put 1

Formula in C2
=IF(A2=A1,IF(C1=1,1,0),IF(C1=1,0,1))

copy down till the end of your data

Then
Select A1:A1000 (all your data)

Home > Conditional Formatting > New Rule > Use a formula to...

and insert this formula
=C1=1

Format button
Fill ---> blue
ok, ok

HTH

M.

 

[raeannb]

Try this..

If the values start in A2
Highlight A2 down to the end of the data

In conditional formatting use the formula
=MOD(SUMPRODUCT(1/COUNTIF(A$2:A2,A$2:A2)),2)=1
Apply your blue fill format

Hope that helps.

It almost worked! It worked for the first few entries, but then highlighted sort of sporadically (only getting the first value in a group, or skipping groups here and there) after that. I'm not sure why, but I ended up figuring something out so I didn't analyze it too carefully. What I ended up going with was a combo of Oaktree and Marcelo (whose responses were essentially the same):

Columns A and B contained data, and the values I wanted to group were found in column B. I made C into a helper column by putting "1" in C2 (C1 was part of the header row) and using the formula:

=IF(B3=B2,IF(C2=1,1,0),IF(C2=1,0,1))

Then filling down. I selected my data in columns A and B, then added conditional formatting with the formula:

=$C2=1

Worked like a charm! Hooray! Thank you!! Y'all are life savers.

Also...My very first conditional format! Exciting.

 

[raeannb]

Now that I'm playing with my beautifully highlighted data, I have another conditional formatting question... is it possible to copy/paste the data in such a way that I can delete the helper column while retaining the formatting? I've tried Paste Special > Formats and the formats still disappear when the helper column does. I can work around the helper column with some effort, I think, butttt...I'd love to not have to.

Also - have I mentioned that you guys rock? No joke. Everyone on this forum deserves a medal.

 

[Jonmo1]

It almost worked! It worked for the first few entries, but then highlighted sort of sporadically (only getting the first value in a group, or skipping groups here and there) after that. I'm

It's a floating point precision problem, I think in the Mod part..

Try

=ROUND(MOD(SUMPRODUCT(1/COUNTIF(A$2:A2,A$2:A2)),2),0)=1