If B1 value is negative, filter column A with all values equal to A1. (possible w/o macro?)

[UWMmakow]

Title is just an example. With a table like this:

DATE	ITEM	QTY ON HAND
10/1	111	1
10/2	111	0
10/3	111	-1
10/1	222	2
10/2	222	1

<tbody>
</tbody>

The last row for item 111 is negative. Item 222 never goes negative. I'd like a filter that shows me all of the rows with item 111.

In practice, there would be hundreds of items and maybe tens of rows per item; some items might go negative only on one day. I want to see all of the rows for all items that go negative at any point.

Taking it another step, I'd like to be able to select a date, and only show items that go negative on or before that date. But one step at a time.

I'm hoping there's a way to do this without a macro, something that I'm just overlooking. It would be ideal if I could keep this document macro free.

If it has to be done via macro, I'd appreciate any tips for writing it. All the code I can think of would be... ugly.

Thanks for reading.

 

[Rijnsent]

Hi UWMmakow,

what about some columns with extra formulas like:

=COUNTIFS(B$2:B$200,B2,C$2:C$200,"<0") -> that should give you all items with a negative element.

If you were to put your "selection From Date" in cell K3, the second formula would be:

=COUNTIFS(B$2:B$200,B2,C$2:C$200,"<0",A$1:A$200,">="&K2) -> that should give you all items with a negative element after that period.

Filtering on those columns will give you the data you're looking for.

Hope that gets you started,

Koen

 

[UWMmakow]

Hi UWMmakow,

what about some columns with extra formulas like:

=COUNTIFS(B$2:B$200,B2,C$2:C$200,"<0") -> that should give you all items with a negative element.

If you were to put your "selection From Date" in cell K3, the second formula would be:

=COUNTIFS(B$2:B$200,B2,C$2:C$200,"<0",A$1:A$200,">="&K2) -> that should give you all items with a negative element after that period.

Filtering on those columns will give you the data you're looking for.

Hope that gets you started,

Koen

Countifs... brilliant. I didn't know that existed. Thank you!

I need to clarify my second request- if I've selected a date, only the items that end with a negative balance should show. So if the selected date is October 15, and the the item goes negative on October 13 but goes back positive (or 0) on October 14, it should not be shown.

I'm going to noodle with this some more but I'd appreciate any other thoughts. Thanks again!

 

[Rijnsent]

Hi UWMmakow,

yup, Microsoft introduced SUMIFS, AVERAGEIFS and COUNTIFS in Office 2007 if I'm not mistaken. Before that time, SUMPRODUCT was the way to go for this kind of calculations. But for your last number you'd need to find the maximum date of a certain item before or equal to your input-date and take the QTY ON HAND of that row... That sounds like a MAX_IF function that doesn't exists, but can be made with an array formula, like in this tutorial: http://blog.contextures.com/archives/2013/11/19/using-max-if-with-multiple-criteria/

Hope that helps,

Koen