Consecutive Sequence Count

[KristinaPan]

Hi everyone! I have been struggling with a specific scenario.
This is a large data sheet and I am trying to figure out consecutive purchases over the years. To identify consecutive length of purchases, which in my case is memberships.

The example shows that Customer1 has been a consecutive member for 1 year, whereas Customer2 is also been a member for 1 year only. Customer5 has been a member for 4 years and Customer6 has been a member for 2 years.

How do I best represent this without having to do manual data analysis. I have tried different functions etc and have completed the count. But cannot find out to do a consecutive count.

Any help is appreciated.

Thank you in adavance

 

[KRice]

Try this:

Mrexcel_20220921.xlsx
	A	B	C	D	E	F
1		2019	2020	2021	2022	Consecutive
2	Cust1	4				1
3	Cust2			7		1
4	Cust3	1	2	5		3
5	Cust4		2		4	1
6	Cust5	4	5	4	8	4
7	Cust6			5	5	2
Sheet2

Cell Formulas
Range	Formula
F2:F7	F2	=MAX(FREQUENCY(IF(B2:E2<>"",COLUMN(B2:E2)),IF(B2:E2="",COLUMN(B2:E2))))

Depending on version of Excel, you may have to confirm formula as array formula...probably Command-Return on Mac (Ctrl-Shift-Enter on Windows).

 

[KristinaPan]

Try this:

Mrexcel_20220921.xlsx
	A	B	C	D	E	F
1		2019	2020	2021	2022	Consecutive
2	Cust1	4				1
3	Cust2			7		1
4	Cust3	1	2	5		3
5	Cust4		2		4	1
6	Cust5	4	5	4	8	4
7	Cust6			5	5	2
Sheet2

Cell Formulas
Range	Formula
F2:F7	F2	=MAX(FREQUENCY(IF(B2:E2<>"",COLUMN(B2:E2)),IF(B2:E2="",COLUMN(B2:E2))))

Depending on version of Excel, you may have to confirm formula as array formula...probably Command-Return on Mac (Ctrl-Shift-Enter on Windows).

This absolutely worked!!! Thank you so much, you have saved me tons of time of manual data analysis

 

[KRice]

You're welcome...I'm happy to help.

 

[KristinaPan]

You're welcome...I'm happy to help.

Hey KRice, one more question, if it is ok to ask this way.

Last part of my analysis is to find out the last year a purchase was made. Really unsure what function will do this for me. Cust1 would be 2019 and Cust3 would be 2021.

 

[KRice]

Here is one approach. What version of Excel are you using?...you should update your profile to show that, as optimal solutions often depend on the version. Two offerings depending on version:

Mrexcel_20220921.xlsx
	A	B	C	D	E	F	G	H
1		2019	2020	2021	2022	Consecutive	Most Recent	Most Recent with 365
2	Cust1	4				1	2019	2019
3	Cust2			7		1	2021	2021
4	Cust3	1	2	5		3	2021	2021
5	Cust4		2		4	1	2022	2022
6	Cust5	4	5	4	8	4	2022	2022
7	Cust6			5	5	2	2022	2022
Sheet2

Cell Formulas
Range	Formula
F2:F7	F2	=MAX(FREQUENCY(IF(B2:E2<>"",COLUMN(B2:E2)),IF(B2:E2="",COLUMN(B2:E2))))
G2:G7	G2	=INDEX($B$1:$E$1,LOOKUP(2,1/(B2:E2<>""),COLUMN(B:E)-COLUMN(B:B)+1))
H2:H7	H2	=XLOOKUP(1,--(B2:E2<>""),$B$1:$E$1,,0,-1)

 

[KristinaPan]

Here is one approach. What version of Excel are you using?...you should update your profile to show that, as optimal solutions often depend on the version. Two offerings depending on version:

Mrexcel_20220921.xlsx
	A	B	C	D	E	F	G	H
1		2019	2020	2021	2022	Consecutive	Most Recent	Most Recent with 365
2	Cust1	4				1	2019	2019
3	Cust2			7		1	2021	2021
4	Cust3	1	2	5		3	2021	2021
5	Cust4		2		4	1	2022	2022
6	Cust5	4	5	4	8	4	2022	2022
7	Cust6			5	5	2	2022	2022
Sheet2

Cell Formulas
Range	Formula
F2:F7	F2	=MAX(FREQUENCY(IF(B2:E2<>"",COLUMN(B2:E2)),IF(B2:E2="",COLUMN(B2:E2))))
G2:G7	G2	=INDEX($B$1:$E$1,LOOKUP(2,1/(B2:E2<>""),COLUMN(B:E)-COLUMN(B:B)+1))
H2:H7	H2	=XLOOKUP(1,--(B2:E2<>""),$B$1:$E$1,,0,-1)

The Index option worked! Thank you so much.
The version I am running is version 16.64 for Mac.

 

[KRice]

You’re welcome.

 

[Peter_SSs]

Another couple of short options below for 'Most Recent', depending on version.

The version I am running is ...

Please record your version in your account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’).

22 09 22.xlsm
	B	C	D	E		G	H
1	2019	2020	2021	2022		Most Recent	Most Recent
2	4					2019	2019
3			7			2021	2021
4	1	2	5			2021	2021
5		2		4		2022	2022
6	4	5	4	8		2022	2022
7			5	5		2022	2022
Most Recent

Cell Formulas
Range	Formula
G2:G7	G2	=LOOKUP(9^9,B2:E2,B$1:E$1)
H2:H7	H2	=MAXIFS(B$1:E$1,B2:E2,"<>")