Repeat guest stay count

[Shahuru]

I wanted to a list of repeat guest count for consecutive stay dates. the back to back stays are considered as one stay. John stay from A - C should be as 1 stay and stay F should show as a new result as 2 since the stay date is not after the last checkout date. For D and E result should show as 1 as it is one stay with multiple dates.

stay	Name	Check in	Check out	Result/ stay
A	John	11/12/2019​	13/12/2019​	1​
B	John	13/12/2019​	14/12/2019​
C	John	14/12/2019​	15/12/2019​
D	James	28/10/2019​	30/10/2019​	1​
E	James	30/10/2019​	01/11/2019​
F	John	01/01/2020​	03/01/2021​	2​
G	Martin	30/10/2019​	01/11/2019​	1​
H	Kim	30/10/2019​	01/11/2019​	1​

Thanks,

 

[Peter_SSs]

Is this what you mean?

21 09 19.xlsm
	A	B	C	D	E
1	stay	Name	Check in	Check out	Result/ stay
2	A	John	12/11/2019	13/12/2019	1
3	B	John	13/12/2019	14/12/2019
4	C	John	14/12/2019	15/12/2019
5	D	James	28/10/2019	30/10/2019	1
6	E	James	30/10/2019	11/01/2019
7	F	John	1/01/2020	1/03/2021	2
8	G	Martin	30/10/2019	11/01/2019	1
9	H	Kim	30/10/2019	11/01/2019	1
Stays

Cell Formulas
Range	Formula
E2:E9	E2	=IF(AND(B2=B1,C2=D1),"",COUNTIFS(B$1:B1,B2,E$1:E1,">0")+1)

 

[Shahuru]

Thanks Peter, can you help me to change the value to Zero for consecutive stays, ei; A, B, C to 0. as in below table James has two stay ( D & E - consecutive stay) to 1 as James has another stay in H , in short All consecutive dates to zero but if has another stay date then the value to 2 based on the stay counts.

Row	Name	Check in	Check out	Result / stay
A	John	11/12/2019​	13/12/2019​	0​
B	John	13/12/2019​	14/12/2019​	0​
C	John	14/12/2019​	15/12/2019​	0​
D	James	28/10/2019​	30/10/2019​	2​
E	James	30/10/2019​	01/11/2019​
F	Kim	01/01/2020​	03/01/2021​	1​
G	Martin	30/10/2019​	01/11/2019​	1​
H	James	30/11/2019​	01/12/2019​	2​

Thanks,

 

[Sufiyan97]

Thanks Peter, can you help me to change the value to Zero for consecutive stays, ei; A, B, C to 0.

Why above should be 0

as in below table James has two stay ( D & E - consecutive stay) to 1

Why above is 1

as James has another stay in H , in short All consecutive dates to zero but if has another stay date then the value to 2 based on the stay counts.

This is what Peter's formula doing

Book1
	A	B	C	D	E
1	Row	Name	Check in	Check out	Result / stay
2	A	John	11/12/2019	13/12/2019	1
3	B	John	13/12/2019	14/12/2019	0
4	C	John	14/12/2019	15/12/2019	0
5	D	James	28/10/2019	30/10/2019	1
6	E	James	30/10/2019	01/11/2019	0
7	F	Kim	01/01/2020	03/01/2021	1
8	G	Martin	30/10/2019	01/11/2019	1
9	H	James	30/11/2019	01/12/2019	2
Sheet3

Cell Formulas
Range	Formula
E2:E9	E2	=IF(AND(B2=B1,C2=D1),0,COUNTIFS(B$1:B1,B2,E$1:E1,">0")+1)

 

[Shahuru]

I want to all conservative stays to be zero and if same guest has multiple stays then the result to 1

 

[Phuoc]

Book1
	A	B	C	D	E
1	stay	Name	Check in	Check out	Result/ stay
2	A	John	12-Nov-19	13-Dec-19	1
3	B	John	13-Dec-19	14-Dec-19
4	C	John	14-Dec-19	15-Dec-19
5	D	James	28-Oct-19	30-Oct-19	1
6	E	James	30-Oct-19	11-Jan-19
7	F	John	01-Jan-20	01-Mar-21	2
8	G	Martin	30-Oct-19	11-Jan-19	1
9	H	Kim	30-Oct-19	11-Jan-19	1
Sheet1

Cell Formulas
Range	Formula
E2:E9	E2	=IF(COUNTIF($B$2:B2,B2)=1,1,IF(LOOKUP(10^35,$D$1:D1/($B$1:B1=B2))=C2,"",LOOKUP(10^35,$E$1:E1/($B$1:B1=B2))+1))

 

[Peter_SSs]

hanks Peter, can you help me to change the value to Zero for consecutive stays, ei; A, B, C to 0. as in below table James has two stay ( D & E - consecutive stay) to 1 as James has another stay in H , in short All consecutive dates to zero but if has another stay date then the value to 2 based on the stay counts.

I'm not sure why Row E in your sample is blank in the final column but otherwise this replicates your results.

21 09 20.xlsm
	A	B	C	D	E
1	Row	Name	Check in	Check out	Result / stay
2	A	John	12/11/2019	13/12/2019	0
3	B	John	13/12/2019	14/12/2019	0
4	C	John	14/12/2019	15/12/2019	0
5	D	James	28/10/2019	30/10/2019	2
6	E	James	30/10/2019	11/01/2019	2
7	F	Kim	1/01/2020	1/03/2021	1
8	G	Martin	30/10/2019	11/01/2019	1
9	H	James	30/11/2019	12/01/2019	2
Stays

Cell Formulas
Range	Formula
E2:E9	E2	=IF(AND(OR(AND(B2=B1,C2=D1),AND(B2=B3,D2=C3)),SUMPRODUCT(--(B$2:B$9=B2),--(B$1:B$8&D$1:D$8<>B$2:B$9&C$2:C$9))=1),0,SUMPRODUCT(--(B$2:B$9=B2),--(B$1:B$8&D$1:D$8<>B$2:B$9&C$2:C$9)))