array formula adjustment

[neowok]

I have the below array formula which filteres for uniques in column L where column P is within 7 days of the date in column R (and K = central and G = S16). It works perfectly ok but does not quite do what I now need.

Column L will contain the same code multiple times, I only ever want it to return once for any given code and this is what it currently does perfectly ok. The complication is that for column P there may be several different dates associated with that one code in column L, and I need only the latest of those dates to be within a week of column R, if the latest date is not within that range, then it returns nothing. At the moment I assume what it will do is stop at the first instance of a code and use whatever date is in column P, but I only want it counted if it is the latest date in column P that has that code.

=SUM(IF(FREQUENCY(IF('[Notice Advanced Find View.xlsx]Notice Advanced Find View'!$K$2:$K$20000="Central",IF(LEFT('[Notice Advanced Find View.xlsx]Notice Advanced Find View'!$G$2:$G$20000,3)="S16",IF('[Notice Advanced Find View.xlsx]Notice Advanced Find View'!$P$2:$P$20000>=$R24,IF('[Notice Advanced Find View.xlsx]Notice Advanced Find View'!$P$2:$P$20000<$R24+7,MATCH('[Notice Advanced Find View.xlsx]Notice Advanced Find View'!$L$2:$L$20000,'[Notice Advanced Find View.xlsx]Notice Advanced Find View'!$L$2:$L$20000,0))))),ROW('[Notice Advanced Find View.xlsx]Notice Advanced Find View'!$L$2:$L$20000)-ROW('[Notice Advanced Find View.xlsx]Notice Advanced Find View'!$L$2)+1),1))

 

[Marcelo Branco]

What exactly you mean by the latest date? The greatest the smallest?
A small data sample (~10 rows) along with expected results would be helpful to fully understand your question and for testing purposes.

M.

 

[neowok]

by latest I mean the biggest. e.g. for code S111_001 in column L, there may be 6 rows with that code, and in those 6 rows column P might have 12/05/17, 15/05/17 and 20/06/17. If column R on my other sheet is 14/05/17 then it should return nothing because the latest date is 20/06/17 and this is more than 7 days after 14/05/17. If the date in column R was say 16/06/17 then it would count 1 as 20/06/17 is the latest of the dates associated with S111_001 and is within 7 days of 16/06/17.

It is diffcult to provide samples as its across multiple files but i can try if it isnt clear.

 

[Marcelo Branco]

Looking at your formula in post #1 it seems to me that all data are in the same file
Notice Advanced Find View.xlsx
in this sheet
Notice Advanced Find View

Now you say there are also data in column R. Of which File/Sheet?

Try to provide a small data sample.

M.

 

[Aladin Akyurek]

@neowok

Is it possible to create an additional range in [Notice Advanced Find View.xlsx]Notice Advanced Find View which would display/list the latest date associated with the codes in column L?

 

[neowok]

Yeah this is what I have done as a workaround now, I used a pivot table on an extra sheet in that file to pull off the latest date for each unique code and will just point my reporting file formula at that table with a simple sumproduct. Not as elegant as having it all in a single formula but it will work fine.

 

[Aladin Akyurek]

Yeah this is what I have done as a workaround now, I used a pivot table on an extra sheet in that file to pull off the latest date for each unique code and will just point my reporting file formula at that table with a simple sumproduct. Not as elegant as having it all in a single formula but it will work fine.

You could have used say in column M...

=MAXIFS($P$2:$P$20000,$L$2:$L$20000,$L2)

and adjust the unique count formula to look at this range.