If the same caller dialed again within a period of N days

[kay89]

Hello,

My worksheet has a column of phone numbers, and another column with the date that this number dialed.
I'd like to know please for every phone number - if this phone number has dialed again within a period of 3 days.

if its true - return 1, if not - return 0.

My data cannot be pre-sorted by first to last date or stuff like that, and i cant use VBA nor pivot tables.

Please help me figure this out..

Thanks a lot!

 

[sandy666]

i cant use VBA nor pivot tables.

what about Power Query?

 

[kay89]

what about Power Query?

Cant.. i have to use third column formula

 

[lrobbo314]

A formula way.

4.xlsx
	A	B	C
1	Number	Date	Date w/in 3 days
2	111-1111	1/26/2021	FALSE
3	111-1111	1/31/2021	FALSE
4	111-1111	2/5/2021	FALSE
5	111-1111	2/10/2021	FALSE
6	111-1111	2/15/2021	FALSE
7	111-1111	2/20/2021	FALSE
8	111-1111	2/25/2021	FALSE
9	111-1111	3/2/2021	FALSE
10	111-1111	3/7/2021	TRUE
11	111-1111	3/10/2021	TRUE
12	111-1111	3/15/2021	FALSE
13	222-2222	3/17/2021	FALSE
Sheet3

Cell Formulas
Range	Formula
C2:C13	C2	=SUMPRODUCT(--(ABS(B2-$B$2:$B$13)<=3)*($A$2:$A$13=A2))-1>0
B3:B10,B12	B3	=B2+5
B11	B11	=B10+3
B13	B13	=B12+2

 

[kay89]

A formula way.

4.xlsx
	A	B	C
1	Number	Date	Date w/in 3 days
2	111-1111	1/26/2021	FALSE
3	111-1111	1/31/2021	FALSE
4	111-1111	2/5/2021	FALSE
5	111-1111	2/10/2021	FALSE
6	111-1111	2/15/2021	FALSE
7	111-1111	2/20/2021	FALSE
8	111-1111	2/25/2021	FALSE
9	111-1111	3/2/2021	FALSE
10	111-1111	3/7/2021	TRUE
11	111-1111	3/10/2021	TRUE
12	111-1111	3/15/2021	FALSE
13	222-2222	3/17/2021	FALSE
Sheet3

Cell Formulas
Range	Formula
C2:C13	C2	=SUMPRODUCT(--(ABS(B2-$B$2:$B$13)<=3)*($A$2:$A$13=A2))-1>0
B3:B10,B12	B3	=B2+5
B11	B11	=B10+3
B13	B13	=B12+2

Brilliant! Thanks a lot??

But.. what if i would have another data column that contains the Subject of the call (text), and now i want to change the calculated formula a bit:

I would like to check if a certain caller called within a period of 3 days, just as before, but- only if he called for the same reason (same subject).
I mean, If he called within a period of 3 days but for a different subject- let it return false.

Thank you very much!!!

 

[lrobbo314]

Try this.

4.xlsx
	A	B	C	D	E
1	Number	Date	Caller	Reason	Date w/in 3 days
2	111-1111	1/26/2021	A	W	FALSE
3	111-1111	1/31/2021	A	W	FALSE
4	111-1111	2/5/2021	A	W	FALSE
5	111-1111	2/10/2021	A	W	FALSE
6	111-1111	2/15/2021	A	W	FALSE
7	111-1111	2/20/2021	A	W	FALSE
8	111-1111	2/25/2021	A	W	FALSE
9	111-1111	3/2/2021	A	W	FALSE
10	111-1111	3/7/2021	A	W	TRUE
11	111-1111	3/10/2021	A	W	TRUE
12	111-1111	3/13/2021	A	W	TRUE
13	222-2222	3/15/2021	B	X	FALSE
14	222-2222	1/26/2021	B	X	TRUE
15	222-2222	1/28/2021	B	X	TRUE
16	222-2222	1/31/2021	B	Y	FALSE
Sheet3

Cell Formulas
Range	Formula
E2:E16	E2	=SUMPRODUCT(--(ABS(B2-$B$2:$B$16)<=3)*($A$2:$A$16=A2)*($C$2:$C$16=C2)*($D$2:$D$16=D2))-1>0

 

[kay89]

Try this.

4.xlsx
	A	B	C	D	E
1	Number	Date	Caller	Reason	Date w/in 3 days
2	111-1111	1/26/2021	A	W	FALSE
3	111-1111	1/31/2021	A	W	FALSE
4	111-1111	2/5/2021	A	W	FALSE
5	111-1111	2/10/2021	A	W	FALSE
6	111-1111	2/15/2021	A	W	FALSE
7	111-1111	2/20/2021	A	W	FALSE
8	111-1111	2/25/2021	A	W	FALSE
9	111-1111	3/2/2021	A	W	FALSE
10	111-1111	3/7/2021	A	W	TRUE
11	111-1111	3/10/2021	A	W	TRUE
12	111-1111	3/13/2021	A	W	TRUE
13	222-2222	3/15/2021	B	X	FALSE
14	222-2222	1/26/2021	B	X	TRUE
15	222-2222	1/28/2021	B	X	TRUE
16	222-2222	1/31/2021	B	Y	FALSE
Sheet3

Cell Formulas
Range	Formula
E2:E16	E2	=SUMPRODUCT(--(ABS(B2-$B$2:$B$16)<=3)*($A$2:$A$16=A2)*($C$2:$C$16=C2)*($D$2:$D$16=D2))-1>0

Perfect.
Thanks a lot!!