Consecutive formula, not counting most recent sequence

[jchick90]

This is my first post, so please go easy on me

I'm trying to calculate the number of consecutive cells in which a value appears, but the most right/recent set of consecutive only.

I'm currently using the following formula, but it's presenting the highest consecutive value, rather than the most recent.

=MAX(FREQUENCY(IF(B2:O2<>"",COLUMN(B2:O2)),IF(B2:O2="",COLUMN(B2:O2))))

The red values are the desired result, and those next to it are what the above formula is currently producing.




Any help is much appreciated!

Thanks so much in advance

 

[Peter_SSs]

Welcome to the MrExcel board!

I suggest that you investigate XL2BB for providing sample data to make it easier for helpers by not having to manually type out sample data to test with.

See how this goes

24 02 22.xlsm
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1
2	0	0	18	11	21	2	254.25			5	20	236.24	2	3	5
3	1	20	20	1	1					2	2				2
4		1		1							.				1
5	3	3	3												3
6															0
7	1	1	1	1	1	1	1	1	1	1	1	1	1	1	14
Recent

Cell Formulas
Range	Formula
P2:P7	P2	=LEN(TEXTAFTER(" "&TRIM(CONCAT(IF(B2:O2=""," ",1)))," ",-1))

 

[jchick90]

Welcome to the MrExcel board!

I suggest that you investigate XL2BB for providing sample data to make it easier for helpers by not having to manually type out sample data to test with.

See how this goes

24 02 22.xlsm
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1
2	0	0	18	11	21	2	254.25			5	20	236.24	2	3	5
3	1	20	20	1	1					2	2				2
4		1		1							.				1
5	3	3	3												3
6															0
7	1	1	1	1	1	1	1	1	1	1	1	1	1	1	14
Recent

Cell Formulas
Range	Formula
P2:P7	P2	=LEN(TEXTAFTER(" "&TRIM(CONCAT(IF(B2:O2=""," ",1)))," ",-1))

Thank you so much!
That works a treat!
Can I ask another favour if possible please?
Say if b1 to p1 were headers, what formula could I use to pull through the header where the most recent sequence starts?

I'm currently using this: =IFERROR(INDEX(B$1:DQ$1,SMALL(IF($B2:$DQ2<>"",COLUMN($B2:$DQ2)-COLUMN(A2)),1)),"")

But it seems to return the first ever value, rather than the first value in the latest sequence.

Thanks so much again

 

[Peter_SSs]

Try this

24 02 22.xlsm
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	Hdr1	Hdr2	Hdr3	Hdr4	Hdr5	Hdr6	Hdr7	Hdr8	Hdr9	Hdr10	Hdr11	Hdr12	Hdr13	Hdr14	Recent Seq	Recent Start
2	0	0	18	11	21	2	254.25			5	20	236.24	2	3	5	Hdr10
3	1	20	20	1	1					2	2				2	Hdr10
4		1		1											1	Hdr4
5	3	3	3												3	Hdr1
6															0
7	1	1	1	1	1	1	1	1	1	1	1	1	1	1	14	Hdr1
Recent

Cell Formulas
Range	Formula
P2:P7	P2	=LEN(TEXTAFTER(" "&TRIM(CONCAT(IF(B2:O2=""," ",1)))," ",-1))
Q2:Q7	Q2	=IFNA(INDEX(B$1:O$1,XMATCH("?*",B2:O2&"",2,-1)-P2+1),"")

 

[jchick90]

Try this

24 02 22.xlsm
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	Hdr1	Hdr2	Hdr3	Hdr4	Hdr5	Hdr6	Hdr7	Hdr8	Hdr9	Hdr10	Hdr11	Hdr12	Hdr13	Hdr14	Recent Seq	Recent Start
2	0	0	18	11	21	2	254.25			5	20	236.24	2	3	5	Hdr10
3	1	20	20	1	1					2	2				2	Hdr10
4		1		1											1	Hdr4
5	3	3	3												3	Hdr1
6															0
7	1	1	1	1	1	1	1	1	1	1	1	1	1	1	14	Hdr1
Recent

Cell Formulas
Range	Formula
P2:P7	P2	=LEN(TEXTAFTER(" "&TRIM(CONCAT(IF(B2:O2=""," ",1)))," ",-1))
Q2:Q7	Q2	=IFNA(INDEX(B$1:O$1,XMATCH("?*",B2:O2&"",2,-1)-P2+1),"")

My hero! Can't thank you enough

 

[Fluff]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Consecutive Count
and Consecutive formula, not counting most recent sequence
There is no need to repeat the link(s) provided above but if you have posted the question at other places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[jchick90]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Consecutive Count
and Consecutive formula, not counting most recent sequence
There is no need to repeat the link(s) provided above but if you have posted the question at other places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

Apologies, I wasn't aware of these