Filtering top with multiple parameters

[Pofski]

Hi everybody,


Would anybody be able to help me with the following problem (see attached file)
What i am trying to obtain is the following. We have a list (column A, B, C and D) that contains location, item, date and result.
I was looking for a way to have the last 3 results from each combination per date.
Now this result should be compared to the parameters in column N. If an item is in the list greater then 1000 has a result that is greater then 1000, then it should be "Flagged".
Same for the list 10000.

the next 3 times, after an item has been greater then the parameter, the results should still be flagged (so conditional formatting rule to have them colored). If the item is good 3 times, there should be no more indicator, apart from the original overdue.

To make it easier, the numbers in column D, are not always numbers, but sometimes noted as ">2000" or "<100"

I hope this makes some sense.

Thank you in advance

 

[Somendra Misra]

@Pofski

Can you show some output in your file.

Regards,

 

[Luke M]

I'm afraid I don't know what you are wanting.
Could you (manually) create a sheet showing the results that you want? Also,

To make it easier, the numbers in column D, are not always numbers, but sometimes noted as ">2000" or "<100"

is incorrect. This actually makes it much harder, since we not have a combination of numbers and text strings in the column. If you can leave them as all numbers, that would be best.

 

[Pofski]

Hi,

first of, i would like to apologize for the remark "To make it easier". It was meant sarcastically, but i am aware that it is easy to misinterpret the sarcasm through written word.
I do mean that it is more difficult. Unfortunately, the data can not be delivered in another way, as these symbols are actually used for other calculations as well.

I have added a file which is my current work in progress on the matter. I tend to first work by separating the formulas, before condensing them again.
In row i to j, i try to find the last 3 dates for each combination.
Using this, i try to find the quantity of those dates, this can be found in column n to p.
I remove the symbols that are not < or > to try to put everything in numbers so that i could use max to find if any of the last three is above the allowed parameters.
I tend to put a filter in that if there is a <, or if there is a > that those would automatically be flagged as to low or to high (if< is found, put as 1 for example)

I hope this already clarifies it a bit as to what i would like to achieve.

Thank you in advance

 

[Luke M]

Hi Pofski,

I think I'm getting closer. I moved all our "control" type tables over to the right, and we have a calculation table in the middle. Additionally, I've added a couple columns to original data table to help us out. Please let me know if I'm on the right track, and/or what else needs to be done.

 

[Pofski]

Luke, this is fantastic already, thank you.
At the moment i am implementing the changes that you made to the original document to see where it ends up.
Just one question, In your file in the columns M, N, O, you are using a combination of sumif and countif, and to be honest, i don't see how it works.
Could you explain this to me please?

 

[Luke M]

Sure thing Pofski.
For other readers, here's the formula:
=IF(ISNUMBER(J3),SUMIFS(DataTable[QtyCleaned],DataTable[HelperCol],LOOKUP(COUNTIF($I$3:$I3,$I3),$R$13:$S$19)&$I3,DataTable[Date],J3),"")

One of the first challenges I saw with the data layout is that the Location in col H only appears at a certain interval, not every row. We could have changed this to make it be at every row, but sometimes appearances really do matter. So we work around it. Since I don't always know where the label will be, we're going to use the LOOKUP and COUNTIF to find out which location we're at. The COUNTIF looks at the product column and sees how many times the product has been listed. If it's been 2 times, then the LOOKUP function goes to the table in R13:S19, and see's the the 2nd location is "Soda Pop".
LOOKUP(COUNTIF($I$3:$I3,$I3),$R$13:$S$19)
We can now use this and concatenate it with the product to use in the next function, the SUMIFS.
LOOKUP(COUNTIF($I$3:$I3,$I3),$R$13:$S$19)&$I3

The SUMIFS functions has several arguements. The first is the range that we will be summing/adding up. This is the QtyCleaned column in the DataTable. After that, we use pairs of arguments; a criteria range, and the criteria to look at. The SUMIFS will only take values from QtyCleaned in rows where the criteria ranges match their respective criteria. We hav etwo criteria. The first is that the Location and Product name match. The second criteria is that the Date matches the date we found in column J:L.
SUMIFS(DataTable[QtyCleaned],DataTable[HelperCol],LOOKUP(COUNTIF($I$3:$I3,$I3),$R$13:$S$19)&$I3,DataTable[Date],J3)

The last functions in our formula are the IF and ISNUMBER. These functions are checking to make sure there's a date in col J:L (by checking to see if there's a number aka a date). If there is no date, then we don't need to do anything, and we display "".

 

[Pofski]

ok, NOW i get it

Thnx a bunch. I never thought about just using the sumifs function, and have always been using the array formula.
Again, thanks a lot.
I got the file to work thanks to you

 

[Luke M]

You are very welcome. Always glad to help.