Identify Repeat Purchases Within a Specific Time

Hi,

I am trying to identify repeat customers with a view to removing the purchase if thecustomer buys the same product within 5 days.

If the same customer buys a different product each day then I would like to count each purchase.

If the same customer buys the same product every day I would only like to count it once every 5 days.

I have tried using COUNTIFS for this. Formula in column D where A contains the Customer, B the Purchase Date and C the Product:

Formula:

Please Login or Register  to view this content.

The issue I have with this is that it captures false positives - so if there was a purchase on Day 1, Day 4 and Day 7 I will get the Day 7 purchase as a repeat of Day 4. In this case what I would like to do is identify Day 4 as the repeat of Day 1. If it is no longer taken in to account then Day 7 should not be identified as a repeat as the previous valid purchase would be Day 1.

Any pointers would be greatly appreciated.

Thanks,

Zega.

Hi Zega, could you upload a sample worksheet with expected results?



_______________________________________________________________________________________________________________________________
1. Click on the * Add Reputation if you think someone helped you
2. Mark your thread as SOLVED when question is resolved

Alvin

Hi,

Sample attached.

Customer in A:A, Purchase Date in B:B, Purchase Item in C:C, Original Calculation in D:D, Desired output in E:E and a description in F:F to help explain the desired outcome. I don't need the data in F:F this is just to try to explain the logic a little.

Thanks,

Zega.


RepeatCustomers.xlsx

Hi Zega, try this solution in D2 and copy it across D2:D10.

Formula:

Please Login or Register  to view this content.

_______________________________________________________________________________________________________________________________
1. Click on the * Add Reputation if you think someone helped you
2. Mark your thread as SOLVED when question is resolved

Alvin

Hi Alvin,

Works perfectly, thanks for your help.

Cheers,

Zega.

Thank you for the feedback, you're welcome



_______________________________________________________________________________________________________________________________
1. Click on the * Add Reputation if you think someone helped you
2. Mark your thread as SOLVED when question is resolved

Alvin