Forum Discussion
How to find customers visiting other shops for the same product in my shop within 14 days
I have data showing the following columns: Customer Name, Date of Visit, Shop (my shop, other shops), Product, Price.
I need to find the customers visiting other shops for the same product in my shop within 14 days.
I tried doing a simple IF(AND,... formula but it doesn't catch customers who's jumping from my shop to another and back for various products within a month.
Your description of column headings is a start. It would be helpful to see how in particular how the various shop visits are noted (yours and others; it sounds as if they're all in the same column).
At the very least, an image showing that. Even better: you could help us help you by posting a copy of your worksheet (or a mockup if the actual contains private info like real names) on OneDrive or GoogleDrive, with a link here that grants access.
mathetes hi there! first of all thank you so much for your prompt reply. Here's a link to a sample data: Mock Data.xlsx
An interesting challenge. I've only been able so far to take it part way, via multiple steps. So I'm posting it in the hopes that one of the more savvy experts around these parts can show both you and me how to deliver the desired result in one single formula/query.
What I've done enables you very slowly to run through the products you've sold, one-by-one, see whether any other store has sold the same to the same customer. Try it, changing the product shown in the yellow box--the only ones YOU have sold per the mock database--and you'll see that there are only two instances where you and another store have sold the same product, and those are not ever to the same customer.
Here's the closest answer (same product within two weeks; but different customer)
But unless I'm missing something, the answer to your original question (from this mock data) ...
I need to find the customers visiting other shops for the same product in my shop within 14 days.
... is "Nobody"
