Populate Based on Date and Criteria

[kumara_faith]

Hi,

I have the following in Table 1 which shows the date the customer visited the outlet.

Table 1
Customer ID	Visit Date
C001	3/7/2023
C002	3/7/2023
C003	3/7/2023
C004	3/7/2023
C005	3/7/2023
C001	4/7/2023
C002	4/7/2023
C003	4/7/2023
C004	4/7/2023
C004	4/7/2023
C005	4/7/2023
C001	5/7/2023
C002	5/7/2023
C003	5/7/2023
C004	5/7/2023
C005	5/7/2023
C001	1/8/2023
C002	1/8/2023
C003	1/8/2023
C004	1/8/2023
C005	1/8/2023
C001	4/8/2023
C002	4/8/2023
C003	4/8/2023
C004	4/8/2023
C005	4/8/2023

I have the following which shows the date the customer logs into our mobile application:

Table 2
Customer ID	Login Date
C004	4/7/2023
C005	5/7/2023
C001	2/8/2023
C002	2/8/2023

I have the following in Table 3:

Table 3
Customer ID	Visit Date	Login After Visit
C001	3/7/2023	0
C002	3/7/2023	0
C003	3/7/2023	0
C004	3/7/2023	0
C005	3/7/2023	0
C001	4/7/2023	0
C002	4/7/2023	0
C003	4/7/2023	0
C004	4/7/2023	2
C004	4/7/2023	2
C005	4/7/2023	0
C001	5/7/2023	0
C002	5/7/2023	0
C003	5/7/2023	0
C004	5/7/2023	0
C005	5/7/2023	3
C001	1/8/2023	4
C002	1/8/2023	4
C003	1/8/2023	0
C004	1/8/2023	0
C005	1/8/2023	0
C001	4/8/2023	0
C002	4/8/2023	0
C003	4/8/2023	0
C004	4/8/2023	0
C005	4/8/2023	0

In the third column in Table 3 ( Login After Visit), I am trying to summarize as follows:

If the customer logs into the mobile apps on or after the visit to the branch, the formula should return the value as the number of times after the visit to the outlet that customer logs into the app. Example fopr customer C004, he visited the branch twice and only then logs into the app. Therefore, the result is 2. For customer C001, he logs into the app after the 4th visit to the outlet, therefore the results is 4. Customer C003 has never logged into the app and therefore the result will be 0.

Is there a way to create a formula for the above ? Appreciate all the help.

 

[Peter_SSs]

Perhaps I am understanding a bit better now. Try this one.

23 07 04.xlsm
	D	E	F	G	H	I
1	Table 2			Table 3
2	Customer ID	Login Date		Customer ID	Visit Date	Login After Visit
3	C004	4/07/2023		C001	3/07/2023	0
4	C005	5/07/2023		C002	3/07/2023	0
5	C001	2/08/2023		C003	3/07/2023	0
6	C002	2/08/2023		C004	3/07/2023	0
7				C005	3/07/2023	0
8				C001	4/07/2023	0
9				C002	4/07/2023	0
10				C003	4/07/2023	0
11				C004	4/07/2023	2
12				C004	4/07/2023	2
13				C005	4/07/2023	0
14				C001	5/07/2023	0
15				C002	5/07/2023	0
16				C003	5/07/2023	0
17				C004	5/07/2023	0
18				C005	5/07/2023	3
19				C001	1/08/2023	4
20				C002	1/08/2023	4
21				C003	1/08/2023	0
22				C004	1/08/2023	0
23				C005	1/08/2023	0
24				C001	4/08/2023	0
25				C002	4/08/2023	0
26				C003	4/08/2023	0
27				C004	4/08/2023	0
28				C005	4/08/2023	0
Visits (2)

Cell Formulas
Range	Formula
I3:I28	I3	=LET(C,G$3:G$28,V,H$3:H$28,LI,VLOOKUP(G3,D$3:E$6,2,0),IF(H3=MAXIFS(V,C,G3,V,"<="&LI),COUNT(UNIQUE(FILTER(V,(C=G3)*(V<=LI),""))),0))

 

[kumara_faith]

Hi Peter,

Thank you and the solution worked. Appreciate your patience and have a great day ahead .

 

[Peter_SSs]

Hi Peter,

Thank you and the solution worked. Appreciate your patience and have a great day ahead .

You're welcome.
The marked solution has been changed accordingly. In your future questions, please mark the post as the solution that actually answered your question, instead of your feedback message as it will help future readers. No further action is required for this thread.