analyse 3 columns, return date of next purchase for same client and product (Index Match)

[cgrynberg]

Hello Everyone,

I have 3 columns of information. Column A with Client-ID, Column B with Product_ID and Column C with Date of Purchase. In Column D, I want to, after analysing the 3 columns, have it return the first date of the next purchase, IF the same client has purchased the same product in a future date. Just to be clear, if the client has purchased the same product in multiple dates, I only need the first date. Tried Index Match but can't figure out how to make the dates work. Hopefully the table below illustrates well what I need. Many thanks.

Client_ID	Product_ID	Date_Purchase	Next Purchase
23178	GBD	27/12/2017	29/12/2017
27844	KSD	27/12/2017	0
12345	DIO	27/12/2017	0
32413	DCV	28/12/2017	0
57665	KJS	28/12/2017	0
12312	EMA	28/12/2017	0
43768	DSL	28/12/2017	0
45823	POD	28/12/2017	0
68893	BHW	28/12/2017	0
23694	ENH	29/12/2017	0
23178	GBD	29/12/2017	03/01/2018
9253	RAK	29/12/2017	0
32791	JXD	29/12/2017	0
91287	XSD	29/12/2017	0
45724	XLK	29/12/2017	0
74856	IDS	30/12/2017	03/01/2018
10002	CVB	30/12/2017	0
23178	ADS	30/12/2017	0
34572	IND	02/01/2018	0
23567	ORA	02/01/2018	0
78932	ODB	02/01/2018	0
82638	POD	02/01/2018	0
67283	GER	02/01/2018	0
27844	CRG	03/01/2018	0
23178	GBD	03/01/2018	0
74856	IDS	03/01/2018	0

[td]10002
[/td]
[td]MVM
[/td]
[td]27/12/2017
[/td]
[td]0
[/td]

 

[Special-K99]

Thinking...

 

[Special-K99]

If you sort the data by column A then B then

in D2
=IF(AND(A3=A2,B3=B2),C3,0)
and copy down the column

 

[cgrynberg]

Hello,

Thank for the answer, I have been busy and took me a while to get back here. Your solution is quite simple, I like it, but I was hoping to do it without the sorting, as I have this info coming in a query, and would like to do it automatically. Your idea of simply looking the next events gave me an idea though: Here is what I came up with:

In column D I have: =MIN(IF(A3:A$65536=A2,IF(B3:B$65536=B2, C3:C$65536,9999999999999),9999999999999)) as an array.
In column F I have =IF(D2=9999999999999,"",D2)

Perhaps not the most elegant, but it works without sorting. Would you have any other suggestion on how to make it better?

Mny thanks for taking the time to help out!

 

[Special-K99]

Nice solution.

Prob not much better but is shorter
Try (untested)

=MIN(IF((A3:A$65536=A2)*(B3:B$65536=B2), C3:C$65536,9999999999999))