Conditional Formatting

[doug730]

Is it possible to create custom bands in a table? For example, I have a few hundred rows of content and the first column is an identifier that looks like this:

ClientA-48102-1
ClientB-51556-1
ClientB-51556-2
ClientB-51556-3
ClientB-59900-1
ClientB-59900-2
ClientC-40938-1
ClientC-40938-2
ClientD-44487-1
ClientD-53229-1

etc...

I want to make it easy to see groups of rows that are related (identified by the client name at the beginning of the string). So A1 would be GREY, A2-A6 would be WHITE, A7-A8 would be GREY, A9-A10 would be WHITE, etc.

I can use a Left/Find formula to extract client names (actual client name lengths vary) into a new column, if needed. I am able to format the first cell in each group using formula "=(a2<>a1)" or conversely I can format subsequent matching cells using "=(a2=a1)" but I'm looking to format full groups. Also, formatting can either alternate or even be unique for each client name (pulled from another table?).

There are currently roughly 40 client names so it would be a ton of work to enter a different conditional format for ever client name.

Hopefully this makes sense...
Thanks in advance!

 

[Eric W]

Welcome to the forum.

Let's say your data is in A:C. Put a heading in row 1. Then select the range A2:C1000 (or whatever the bottom row of your data is). Click Conditional Formatting > New Rule > Use a formula > and enter:

=AND(MOD(SUMPRODUCT(--($A$1:$A1<>$A$2:$A2)),2),$A2<>"")

Click format... and choose your fill color.

 

[doug730]

Eric - thank you! This is perfect! I've been trying to figure this out off-and-on for weeks.

 

[Eric W]

Glad to help!

Whoever first invented that formula was quite clever indeed.