Tjyylli

Deleted Deleted Deleted Deleted Deleted Deleted Deleted Deleted Deleted Deleted Deleted Deleted Deleted Deleted Deleted Deleted Deleted Deleted Deleted Deleted Deleted Deleted Deleted Deleted Deleted Deleted Deleted Deleted Deleted Deleted Deleted Deleted Deleted Deleted Deleted Deleted Deleted Deleted Deleted Deleted Deleted Deleted Deleted Deleted Deleted Deleted Deleted Deleted Deleted Deleted Deleted Deleted Deleted Deleted Deleted

Perhaps I’m misunderstanding something, but this seems to be fairly simple. 
My understanding of the question is that a row represents a reorder
if there is at least one row above the current one
that has the same Customer Name (Column B) as the current row,
the same Product Name (Column D), and a Transaction Date (Column A)
within the past six months of the current Transaction Date. 
Columns C, E and F can be ignored. 
I assume that the rows are sorted by Transaction Date
(although I guess I don’t need to make that assumption).

The Transaction Date criterion is the “hardest” (I use that term loosely). 
A past date is within the past six months of A2 if it is

> EDATE(A2,-6)

So, to count the rows through the current one
that satisfy the three criteria, we use

=COUNTIFS(A$2:A2, ">" & EDATE(A2,-6), B$2:B2, B2, D$2:D2, D2)

The A$2:A2 notation is interesting. 
It represents a range that starts in Row 2 and ends in the current row;
i.e., everything up through (and including) the current row. 
This count will always be at least 1, because the current row counts. 
If it’s greater than 1,
there was at least one previous row that also matched. 
So the answer is to enter

=IF(COUNTIFS(A$2:A2, ">" & EDATE(A2,-6), B$2:B2, B2, D$2:D2, D2)<=1, "New Order", "Reorder")

into H2 (or wherever you want it) and drag/fill down.

If the rows might be out of order, we need to search the entire table
and test that the date is less than the current date:

=IF(COUNTIFS(A$2:A$99, ">" & EDATE(A2,-6), A$2:A$99, "<" & A2,

                                           B$2:B$99, B2, D$2:D$99, D2)=0, "New Order", "Reorder")

where I’m using 99 to represent the last row of the data. 
I changed the test from <=1 to =0
because the < A2 test eliminates the current row. 
If your data might include multiple rows
with the same Customer Name and Product Name,
and the exact same Transaction Date,
please specify how they should be handled.

Oh, you're gonna love this one.

I'll break it down into steps because the final formula is going to be fairly complex.

Setup

I created a worksheet in the format you specified. I've added three columns to piece out what we need to do, which I'll describe individually below. Note that I've frozen the top row and scrolled down a bit in this first screenshot.

Step 1: Establish range to search

First we need to know when 6 months ago was (and where in the spreadsheet that date is located). In each record, we'll use EDATE to find out.

In cell G2:

VLOOKUP(EDATE(A2,-6),A:A,1,TRUE)

This is great - this gives us the last sales record we have that was more than 6 months ago. Note that if you have infrequent sales (ie. big gaps between sales records), the way that I've written this does mean you might 'catch' false positives, if the last order of the item in question was, say, 6 months and a week ago AND there were no orders at all between 6 months and a week ago and exactly 6 weeks ago. I decided this was 'close enough' for you.

The only other flaw is there are some unpleasing-looking errors near the top of the sheet, because for the first record (ie. row 2) there are no 6-months-ago records at all. So let's wrap with an IFERROR:

=IFERROR(VLOOKUP(EDATE(A2,-6),A:A,1,TRUE),$A$2)

In other words, tell me when the most recent order more than 6 months ago was, unless there isn't one, in which case just give me the first order we have record of.

This formula copies down. That's here:

Step 2: Turn that range-starting data into a cell location

In cell H2:

=MATCH(G2,A:A,0)

This one's easy. In what ordinal location in column A do we find the 6-months-ago date we established in Step 1?

This formula copies down. Here we are:

Step 3: Do it

Let's skip ahead and put this into, how about, I21, so we can see how it works.

=INDEX(INDIRECT("A"&H21&":D"&ROW()-1),MATCH(D21,INDIRECT("D"&H21&":D"&ROW()-1),0),1)

This is, at base, just a simple INDEX MATCH search. But what we've done is replace the cell references of the arrays of those functions with INDIRECT references to the value we just seeded into column H.

In my sample data, row 21 is a sales record with a date of 10/1/2018. Column G finds the most recent sales record more than 6 months prior to that date, which in my sample date happens to be 4/1/2018. Column H pegs the (first) location of that date in column A at the 8th row. Thus INDIRECT("A"&H21& says to start the array for the search at the row in A identified by the value in H. Nice! And the other half of the INDIRECT, ":D"&ROW()-1) says to end the array at the row in D that's one row above the record we're presently in.

In other words, you could write

=INDEX(A1:D999,MATCH(D21,D1:D9999,0),1)

To find the first incidence of your product code in D over the ENTIRE sales ledger - but we don't want to do that. So we replace the cell references with dynamic ranges that we've used the previous two steps to generate. With me?

So that looks like this:

Once we copy that formula (wrapped in another IFERROR for appearances) up to row 2 and back down, you've got:

There's a false positive on the first row. I'm going to live with that, too.

Step 4: Combine

So the formula in I references H, which references G. Reverse expansion nets a final formula that reads:

=IFERROR(INDEX(INDIRECT("A"&MATCH(IFERROR(VLOOKUP(EDATE(A2,-6),A:A,1,TRUE),$A$2),A:A,0)&":D"&ROW()-1),MATCH(D2,INDIRECT("D"&MATCH(IFERROR(VLOOKUP(EDATE(A2,-6),A:A,1,TRUE),$A$2),A:A,0)&":D"&ROW()-1),0),1),"NEW ORDER")

And looks like:

So, if you like, you can just copy that formula into your 'reorder' column in row 2 and copy down.

NB.

1. You're going to use Format Painter on your output field, because the formulas as written will treat the text dates in A as date serial numbers, and return a serial number as well.


2. Note the weakness in Step 1 - you can see in the screenshot of the helper1 field in Step 3 that in my sample data, the 6-months-ago date for 10/1 was 4/1 and the 6-months-ago date for 10/5 was also 4/1, because there were no orders at all between 4/1 and 4/5. This could potentially call out false positives.


3. Again, as discussed in Step 3 there's also going to be a false positive on the first sales record using this method.