Conditional format groups of rows based on multiple criteria

[wiseone]

I have some data as listed in the below table with an example of how it would conditionally format.

It should format the rows based on the following criteria:

1. The group of items is highlighted based on column B* IF:
   • The first row of the group in cell column C is not blank.
   • The data in columns D and E for the group are not blank.
2. There is a blank row between groups, this should not highlighted.

*I would prefer it to group just based on Columns C D and E, and recognized they are separated by blank rows, but I don't think this is possible...so I instead suggested column B as a means to group them....if you think you can suggest a way to ignore column B....I'd love to see your suggestion.

Thanks in advance for your help!

 

[Eric W]

This basically counts the number of blank rows in column A:

Book2
	A	B	C	D	E
1	Header 1	Header 2	Header 3	Header 4	Header 5
2	AAA	21-000	$ 100.00	$ 123.00	$ 2,342.00
3	BBB	21-000		$ 456.00	$ 23,423.00
4	CCC	21-000		$ 789.00	$ 3,242.00
5
6	DDD	21-001	$ 15.00	$ 5,463.00	$ 564.00
7	EEE	21-001			$ 3,563.00
8	EEE	21-001		$ 564.00	$ 7,657.00
9
10	FFF	21-002	$ 456.00	$ 675.00	$ 968.00
11	FFF	21-002		$ 909.00	$ 69.00
12	FFF	21-002		$ 6,574.00	$ 7,675.00
13
14	GGG	21-003		$ 5,463.00	$ 564.00
15	HHH	21-003		$ 89.00	$ 3,563.00
16	HHH	21-003		$ 564.00	$ 7,657.00
17
18	JJJ	21-004
19	JJJ	21-004
20	KKK	21-004	$ 123.00
21	KKK	21-004
22
23	LLL	21-005	$ 456.00
24	MMM	21-005	$ 789.00
Sheet14

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:E24	Expression	=($A2<>"")*(MOD(SUMPRODUCT(--($A$1:$A1="")),2)=0)	text	NO

 

[wiseone]

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

Hi Eric, I'm not sure what this is supposed to do, but all its doing in my spreadsheet is hilighting what appears to be random rows. It doesn't appear there are conditions which apply to columns B,C,D or E. Am I supposed to append this formula with some more information?

 

[wiseone]

Hi Eric, I'm not sure what this is supposed to do, but all its doing in my spreadsheet is hilighting what appears to be random rows. It doesn't appear there are conditions which apply to columns B,C,D or E. Am I supposed to append this formula with some more information?

Missed the edit time.... I played with it abit more and I see what happened..... Here was my edited message.

Hi Eric, I'm not sure what this is supposed to do, but all its doing in my spreadsheet is highlighting what appears to be every other group. It doesn't appear there are conditions which apply to columns B,C,D or E. Am I supposed to append this formula with some more information?

In my sample, the groups 21-001 and 21-003 are missing information in cells D7 and C14, that is why they are not hilighted.

 

[wiseone]

Bump....anyone able to help with this one?

 

[Eric W]

Sorry, I've been away for a while, and it looks like I misread your requirements. How about:

Book2
	A	B	C	D	E
1	Header 1	Header 2	Header 3	Header 4	Header 5
2	AAA	21-000	$ 100.00	$ 123.00	$ 2,342.00
3	BBB	21-000		$ 456.00	$ 23,423.00
4	CCC	21-000		$ 789.00	$ 3,242.00
5
6	DDD	21-001	$ 15.00	$ 5,463.00	$ 564.00
7	EEE	21-001			$ 3,563.00
8	EEE	21-001		$ 564.00	$ 7,657.00
9
10	FFF	21-002	$ 456.00	$ 675.00	$ 968.00
11	FFF	21-002		$ 909.00	$ 69.00
12	FFF	21-002		$ 6,574.00	$ 7,675.00
13
14	GGG	21-003		$ 5,463.00	$ 564.00
15	HHH	21-003		$ 89.00	$ 3,563.00
16	HHH	21-003		$ 564.00	$ 7,657.00
17
18	JJJ	21-004
19	JJJ	21-004
20	KKK	21-004	$ 123.00
21	KKK	21-004
22
23	LLL	21-005	$ 456.00
24	MMM	21-005	$ 789.00
25
26
27
28
29
30
31
Sheet14

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:E31	Expression	=AND($A2<>"",INDEX($C:$C,MATCH($B2,$B:$B,0))<>"",COUNTIF($B:$B,$B2)*2=COUNTIFS($B:$B,$B2,$D:$D,"<>")+COUNTIFS($B:$B,$B2,$E:$E,"<>"))	text	NO

 

[wiseone]

Sorry, I've been away for a while, and it looks like I misread your requirements. How about:

Book2
	A	B	C	D	E
1	Header 1	Header 2	Header 3	Header 4	Header 5
2	AAA	21-000	$ 100.00	$ 123.00	$ 2,342.00
3	BBB	21-000		$ 456.00	$ 23,423.00
4	CCC	21-000		$ 789.00	$ 3,242.00
5
6	DDD	21-001	$ 15.00	$ 5,463.00	$ 564.00
7	EEE	21-001			$ 3,563.00
8	EEE	21-001		$ 564.00	$ 7,657.00
9
10	FFF	21-002	$ 456.00	$ 675.00	$ 968.00
11	FFF	21-002		$ 909.00	$ 69.00
12	FFF	21-002		$ 6,574.00	$ 7,675.00
13
14	GGG	21-003		$ 5,463.00	$ 564.00
15	HHH	21-003		$ 89.00	$ 3,563.00
16	HHH	21-003		$ 564.00	$ 7,657.00
17
18	JJJ	21-004
19	JJJ	21-004
20	KKK	21-004	$ 123.00
21	KKK	21-004
22
23	LLL	21-005	$ 456.00
24	MMM	21-005	$ 789.00
25
26
27
28
29
30
31
Sheet14

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:E31	Expression	=AND($A2<>"",INDEX($C:$C,MATCH($B2,$B:$B,0))<>"",COUNTIF($B:$B,$B2)*2=COUNTIFS($B:$B,$B2,$D:$D,"<>")+COUNTIFS($B:$B,$B2,$E:$E,"<>"))	text	NO

Works like a charm. Thank you so much!

 

[wiseone]

Works like a charm! Thank you so much!

 

[Eric W]

Happy to help!

 

[wiseone]

One more question regarding this. This formula is demanding as its checking all rows in the spreadsheet. I only need it to work for about 500 rows. However, I often insert rows, or delete rows or move rows around.

Is there a way to make the conditional format stay "clean" so it doesn't duplicate and adjust as I cut/paste/insert/delete rows?

Here is the final formula I tried based on your solution (which doesn't work unfortunately)...only hilighting 1st row). It works if I don't specify rows)

=AND($A6<>"",INDEX($AI6:$AI500,MATCH($R6,$R6:$R500,0))<>"",COUNTIF($R6:$R500,$R6)*3=COUNTIFS($R6:$R500,$R6,$AC6:$AC500,"<>")+COUNTIFS($R6:$R500,$R6,$AD6:$AD500,"<>")+ COUNTIFS($R6:$R500,$R6,$Ak6:$Ak500,"<>"))