Google sheets uniqueif without a helper column

[Jaque_Thay]

Hi all,

I'm reworking an existing google sheet that manages inventory. There are currently two tables in one tab: one for items in, one for items out (combining the tables isn't possible as each in/out table has other data too).

Item In (A)	Quantity In (B)	Item Out (C)	Quantity Out (D)
Apple	5	Apple	3
Grape	4	Apple	4
Banana	1	Pear	2
Pear	3
Apple	2
Banana	1

It has a summary sheet that uses unique() to make a list of items.

Item (E)	Count (F)
=unique(A2:A)	=arrayformula(sumif(A2:A, E2:E, B2:B)-sumif(C2:C, E2:E, D2:D)

Some items may go out of stock - with unique(), they continue to be listed in the summary. I'd like to use something like unique(filter(A2:A,[sum of this item] >0)) which would remove the irrelevant items from the summary list. At the moment, with my testing, I'm managing it with a hidden helper table which essentially duplicates E and F so the formula in E2 would be =UNIQUE(filter('HiddenHelper'E2:E, 'HiddenHelper'F2:F >0))

Obviously, this is silly because I've now got two sets of near identical data, but I can't figure out the next step in optimising this. Should I be looking at a virtual table within the formula, or is there some simple method I've overlooked for calculating the number of Apples etc from the original two tables?

 

[Jaque_Thay]

I think this is one of those situations where explaining the problem helps you arrive at the solution. This is what I've arrived at, which seems to work perfectly.

=UNIQUE(filter(A2:A,sumif(A2:A,A2:A,B2:B)-sumif(C2:C,A2:A,D2:D)>0))

Hopefully useful for anyone facing similar issues in the future!