Last non-blank cell in CF (Conditional Formatting)

[AttiM]

Hi, this is my very first post here, thanks in advance for every advice.

I need a solution for highlighting items which are in group, based on the number of connected items in the first row of group.
Look at the bordered cells in B2:C14 in this Mini Sheet.
The goal is to highlight cells in column B based on the numbers in column C - if there is a number X in a row in column C we need to highlight the cells in column B in the same row and in the next X-1 rows according to X, without using any auxiliary column for calculating, using Condition Formatting only.
You can see the correctly highlighted cells in column B.

CF-last nonempty cells.xlsx
	A	B	C	D	E	F	G
1
2		Items	connected items		row number of last non-empty cell above (and include) the current row	value of last non-empty cell above (and include) the current row	condition: current row number >= E and <= E+F
3		Aaaa			0	0	FALSE
4		Bbbb			0	0	FALSE
5		CcccA	2		5	2	TRUE
6		CcccB			5	2	TRUE
7		Dddd			5	2	FALSE
8		Eeee			5	2	FALSE
9		FfffA	4		9	4	TRUE
10		FfffB			9	4	TRUE
11		FfffC			9	4	TRUE
12		FfffD			9	4	TRUE
13		Gggg			9	4	FALSE
14		Hhhh			9	4	FALSE
15					=IFERROR(LOOKUP(2,1/(INDIRECT("C4:C"&ROW())<>""),ROW(INDIRECT("C4:C"&ROW()))),0)	=IFERROR(LOOKUP(2,1/(INDIRECT("C4:C"&ROW())<>""),INDIRECT("C4:C"&ROW())),0)	=AND(ROW()>=E14,ROW()<E14+F14)
Sheet1

Cell Formulas
Range	Formula
E3:E14	E3	=IFERROR(LOOKUP(2,1/(INDIRECT("C4:C"&ROW())<>""),ROW(INDIRECT("C4:C"&ROW()))),0)
F3:F14	F3	=IFERROR(LOOKUP(2,1/(INDIRECT("C4:C"&ROW())<>""),INDIRECT("C4:C"&ROW())),0)
G3:G14	G3	=AND(ROW()>=E3,ROW()<E3+F3)

My steps:
1. Find the row number of last non-empty cell above (and include) the current row in column E.
I can use the "classic" formula for this:
=IFERROR(LOOKUP(2,1/(INDIRECT("C4:C"&ROW())<>""),ROW(INDIRECT("C4:C"&ROW()))),0)
or the newer method:
=IFERROR(XLOOKUP(TRUE,INDIRECT("C3:C"&ROW())<>"",ROW(INDIRECT("C3:C"&ROW())),,,-1),0)
2. Get the value of this last non-empty cell in column F:
=IFERROR(LOOKUP(2,1/(INDIRECT("C4:C"&ROW())<>""),INDIRECT("C4:C"&ROW())),0)
3. Create the condition in column G: If current row number >= E and < E+F:
=AND(ROW()>=E3,ROW()<E3+F3)
As you can see in column G, the method is correct, we can see TRUE in every row where the highlighting is needed.

Applying these formulas in Conditional Formatting:
1. referencing directly to the logical values in column G: it's OK (see the screenshot )
2. using the formula in CF which is in column G (=AND(ROW()>=E3,ROW()<E3+F3)): Fine too ()

3. replace the references for column E and F in this formula =AND(ROW()>=E3,ROW()<E3+F3):
=AND(ROW()>=IFERROR(LOOKUP(2;1/(INDIRECT("C3:C6")<>"");ROW(INDIRECT("C3:C"&ROW())));0);
ROW()<IFERROR(LOOKUP(2;1/(INDIRECT("C3:C6")<>"");ROW(INDIRECT("C3:C"&ROW())));0)
+ IFERROR(LOOKUP(2;1/(INDIRECT("C3:C6")<>"");INDIRECT("C3:C"&ROW()));0))
and using this complex formula in CF: it doesn't work!

Spending a lot of time to figure out why is this failure and what can be the solution, I found that the root of the problem is using ROW() in INDIRECT.
For testing I replace INDIRECT("C3:C"&ROW()) with INDIRECT("C3:C6"), and it works - of course only for the first group

I believe that using ROW() in the formula in CF is must needed to achieve my goal.
And I don't want to use auxiliary column for calculating the logical values for CF (like here in column G), because in fact I have thousands of rows and it takes too much time for calculating cells in column G every time when anything has changed in column C.

So, if you have enough time for experimentations and searching for a solution for my problem I would highly appreciate!
Thanks a lot,
AttiM

 

[bobsan42]

How are the values in column C filled?
Is there some condition or formula or code that that does it or they are filled manually?
Are there any similar parts between the connected items in column B, or this is just for example?

 

[AttiM]

How are the values in column C filled?
Is there some condition or formula or code that that does it or they are filled manually?
Are there any similar parts between the connected items in column B, or this is just for example?

Hi bobsan42, thanks for quick reply.
In reality there is no any correlation between the members, neither similar parts nor anything (you can see just examples here). The connection between is based only on the number of group members filled manually by user in column C (and they should be in consecutive positions).
AttiM

 

[bobsan42]

And you absolutely should not use a helper column? Such approach would simplify things a lot.

 

[AttiM]

And you absolutely should not use a helper column? Such approach would simplify things a lot.

Yes I know, the solution is ready if I can use helper column. As I mentioned, I have thousands of rows and calculating the changes in column C takes 1.5 sec for every 1.000 rows. So the user would have to wait a long time if we use the easier method.

 

[bobsan42]

Don't think CF will be much faster.
I modified your solution a bit. Basically ditched the INDIRECT and some othe small things. And it works for me.
The only drawback is there will be no distinction between groups if some of them are consecutive.

CF Book1.xlsx
	A	B	C
1
2		Items	connected items
3		Aaaa
4		Bbbb
5		CcccA	2
6		CcccB
7		Dddd
8		Eeee
9		FfffA	4
10		FfffB
11		FfffC
12		FfffD
13		Gggg
14		Hhhh
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B3:B14	Expression	=AND(ROW($B3)>=IFERROR(LOOKUP(2,1/(ISNUMBER($C$1:$C3)),ROW($C$1:$C3)),0),ROW($B3)<IFERROR(LOOKUP(2,1/(ISNUMBER($C$1:$C3)),ROW($C$1:$C3)),0)+IFERROR(LOOKUP(2,1/(ISNUMBER($C$1:$C3)),$C$1:$C3),0))	text	NO

There are 3 parts to it as in your solution:
first find the last row with number in col. C

=IFERROR(LOOKUP(2,1/(ISNUMBER($C$1:$C3)),ROW($C$1:$C3)),0)

then get the actual number:

=IFERROR(LOOKUP(2,1/(ISNUMBER($C$1:$C3)),$C$1:$C3),0)

then check if the row cell of the cell in col. B is between the first and the sum of the two:

=AND(ROW($B3)>=IFERROR(LOOKUP(2,1/(ISNUMBER($C$1:$C3)),ROW($C$1:$C3)),0),ROW($B3)<IFERROR(LOOKUP(2,1/(ISNUMBER($C$1:$C3)),ROW($C$1:$C3)),0)+IFERROR(LOOKUP(2,1/(ISNUMBER($C$1:$C3)),$C$1:$C3),0))

 

[AttiM]

Don't think CF will be much faster.
I modified your solution a bit. Basically ditched the INDIRECT and some othe small things. And it works for me.
The only drawback is there will be no distinction between groups if some of them are consecutive.

CF Book1.xlsx
	A	B	C
1
2		Items	connected items
3		Aaaa
4		Bbbb
5		CcccA	2
6		CcccB
7		Dddd
8		Eeee
9		FfffA	4
10		FfffB
11		FfffC
12		FfffD
13		Gggg
14		Hhhh
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B3:B14	Expression	=AND(ROW($B3)>=IFERROR(LOOKUP(2,1/(ISNUMBER($C$1:$C3)),ROW($C$1:$C3)),0),ROW($B3)<IFERROR(LOOKUP(2,1/(ISNUMBER($C$1:$C3)),ROW($C$1:$C3)),0)+IFERROR(LOOKUP(2,1/(ISNUMBER($C$1:$C3)),$C$1:$C3),0))	text	NO

There are 3 parts to it as in your solution:
first find the last row with number in col. C

=IFERROR(LOOKUP(2,1/(ISNUMBER($C$1:$C3)),ROW($C$1:$C3)),0)

then get the actual number:

=IFERROR(LOOKUP(2,1/(ISNUMBER($C$1:$C3)),$C$1:$C3),0)

then check if the row cell of the cell in col. B is between the first and the sum of the two:

=AND(ROW($B3)>=IFERROR(LOOKUP(2,1/(ISNUMBER($C$1:$C3)),ROW($C$1:$C3)),0),ROW($B3)<IFERROR(LOOKUP(2,1/(ISNUMBER($C$1:$C3)),ROW($C$1:$C3)),0)+IFERROR(LOOKUP(2,1/(ISNUMBER($C$1:$C3)),$C$1:$C3),0))

bobsan42: Thank you so much! It really works! So, the key is: don't use indirect in CF.
You're right it's a bit faster but not much. I will try to add another criteria: because the number of elements in a group won't be more than 50, so it's enough to examine only 50 rows above the current row in CF.
Thanks a lot,
Attila

 

[bobsan42]

bobsan42: Thank you so much! It really works! So, the key is: don't use indirect in CF.
You're right it's a bit faster but not much. I will try to add another criteria: because the number of elements in a group won't be more than 50, so it's enough to examine only 50 rows above the current row in CF.
Thanks a lot,
Attila

Team work .
Be careful with this modification though. You will run in range trouble with the first 50 rows or so and you won't gain much anyway, if anything. It is the amount of cells that have to be formatted that counts the most.
And yes - there are functions which cannot be used with CF. It is not only INDIRECT.