How to determine if an order was a new order or a reorder for a specific product and cust.

[Druba]

Data I possess: Transaction Date (A:A), Customer Name (B:B), Sales Order Number (C:C), Product Name (D:D), Units (E:E), Revenue (F:F)

New order would be anything that the customer hasn't ordered in the past 6 months or ever.

A reorder would be if the customer had purchased that specific product in the past 6 months.

I can't wrap my head around how to put this logic into an excel formula.

[lazyme]

If Sum of Units from Customer Name >0 then a re-order.

Array formula below:

Please Login or Register  to view this content.

[JeteMc]

Hello Druba and Welcome to Excel Forum.
It would seem to me that the formula should be something along the lines of:

Please Login or Register  to view this content.

Note that the formula assumes the customer name being looked up is in cell G2.
If this doesn't do what you want, please upload a small desensitized sample by clicking on the GO ADVANCED button below the Quick Reply window and then scrolling down to Manage Attachments to open the upload window.
Make sure there is enough data to demonstrate your need, however we probably won't need more than a few dates for a couple of customers.
Make sure your desired results are shown, mock them up manually.
Let us know if you have any questions.

[Druba]

Thank you for your work on this, but that didn't work for me. I attached a sample sheet in the way you described.

Another problem for me would be scaling after I have a good formula for this. How can I expand it to hundreds of thousands of row without Excel giving up on me?

[JeteMc]

The following formula yields the same results as those that were manually placed in column F:

Please Login or Register  to view this content.

Paste the formula into F2 and double click the fill handle to copy down.
Let us know if you have any questions.

[Druba]

Thank you, worked like a charm!

[JeteMc]

You're Welcome and thank you for the feedback. Please take a moment to mark the thread as 'Solved' using the thread tools menu above your first post. I hope that you have a blessed day.