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.

[table="width: 500, class: grid, align: center"]
[tr]
[td]Client_ID
[/td]
[td]Product_ID
[/td]
[td]Date_Purchase
[/td]
[td]Next Purchase
[/td]
[/tr]
[tr]
[td]10002
[/td]
[td]MVM
[/td]
[td]27/12/2017
[/td]
[td]0
[/td]
[/tr]
[tr]
[td]23178
[/td]
[td]GBD
[/td]
[td]27/12/2017
[/td]
[td]29/12/2017
[/td]
[/tr]
[tr]
[td]27844[/td] [td]KSD[/td] [td]27/12/2017[/td] [td]0[/td]

[/tr]
[tr]
[td]12345[/td] [td]DIO[/td] [td]27/12/2017[/td] [td]0[/td]

[/tr]
[tr]
[td]32413[/td] [td]DCV[/td] [td]28/12/2017
[/td] [td]0[/td]
[/tr]
[tr]
[td]57665[/td] [td]KJS[/td] [td]28/12/2017
[/td] [td]0[/td]

[/tr]
[tr]
[td]12312[/td] [td]EMA[/td] [td]28/12/2017
[/td] [td]0[/td]

[/tr]
[tr]
[td]43768[/td] [td]DSL[/td] [td]28/12/2017
[/td] [td]0[/td]

[/tr]
[tr]
[td]45823[/td] [td]POD[/td] [td]28/12/2017
[/td] [td]0[/td]

[/tr]
[tr]
[td]68893[/td] [td]BHW[/td] [td]28/12/2017
[/td] [td]0[/td]

[/tr]
[tr]
[td]23694[/td] [td]ENH[/td] [td]29/12/2017
[/td] [td]0[/td]

[/tr]
[tr]
[td]23178[/td] [td]GBD[/td] [td]29/12/2017
[/td] [td]03/01/2018[/td]

[/tr]
[tr]
[td]9253[/td] [td]RAK[/td] [td]29/12/2017
[/td] [td]0[/td]

[/tr]
[tr]
[td]32791[/td] [td]JXD[/td] [td]29/12/2017
[/td] [td]0[/td]
[/tr]
[tr]
[td]91287[/td] [td]XSD[/td] [td]29/12/2017
[/td] [td]0[/td]

[/tr]
[tr]
[td]45724[/td] [td]XLK[/td] [td]29/12/2017
[/td] [td]0[/td]

[/tr]
[tr]
[td]74856[/td] [td]IDS[/td] [td]30/12/2017
[/td] [td]03/01/2018[/td]

[/tr]
[tr]
[td]10002[/td] [td]CVB[/td] [td]30/12/2017
[/td] [td]0[/td]

[/tr]
[tr]
[td]23178[/td] [td]ADS[/td] [td]30/12/2017
[/td] [td]0[/td]

[/tr]
[tr]
[td]34572[/td] [td]IND[/td] [td]02/01/2018
[/td] [td]0[/td]

[/tr]
[tr]
[td]23567[/td] [td]ORA[/td] [td]02/01/2018
[/td] [td]0[/td]

[/tr]
[tr]
[td]78932[/td] [td]ODB[/td] [td]02/01/2018
[/td] [td]0[/td]

[/tr]
[tr]
[td]82638[/td] [td]POD[/td] [td]02/01/2018
[/td] [td]0[/td]

[/tr]
[tr]
[td]67283[/td] [td]GER[/td] [td]02/01/2018
[/td] [td]0[/td]

[/tr]
[tr]
[td]27844[/td] [td]CRG[/td] [td]03/01/2018
[/td] [td]0[/td]

[/tr]
[tr]
[td]23178[/td] [td]GBD[/td] [td]03/01/2018
[/td] [td]0[/td]

[/tr]
[tr]
[td]74856[/td] [td]IDS[/td] [td]03/01/2018
[/td] [td]0[/td]

[/tr]
[/table]

 

[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))