yogi_Create A Summary Sheet To List All Unique EPIC That Still Has Opening Positions

                                         Google Spreadsheet   Post  #1593
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Apr-09-2014
post by VincentCw: (https://productforums.google.com/forum/#!mydiscussions/docs/-nZUG6OxC_I)
How do I filter by column and selected rows?

Type	EPIC	Cost	Quantity
Buy	LON:RMG	120.00	22
Buy	LON:RMG	123.00	30
Buy	LON:COST	294.06	33
Buy	LON:AVV	2,260.00	8
Buy	LON:NRR	283.48	70
Buy	LON:COST	225.00	11
Buy	LON:NRR	276.63	5
Sell	LON:AVV	1,960.40	8

Hi guys, initially I only wanted to get unique EPIC which works fine (without involving the sell) with the following formula:

=unique(FILTER(B2:B,NOT(ISBLANK(B2:B))))

However, I now that I have included sell I wanted to select unique EPIC with no current open trades, meaning to get calculate the sum of all Buy and minus sum of all Sell for the unique epic and only return if the total sum is more than 0.

I've tried using the filter function but the filter isn't able to 'loop through' each unique epic to cross check against it's difference in the sum of buys and sells.

Any help is much appreciated.

---

Hi Yogi,

  Thanks for your quick response.

https://docs.google.com/spreadsheets/d/1lYDHjFCSSUSkGTCSreI6bM3ORtYYD8KUxGvBS0eGVJ4/edit?usp=sharing

I've taken out the involving columns and rows and place it into a sheet.

EPIC is just a stock reference number that's used by stock exchange or even googlefinance() function.

a) i'm trying to list all unique epic that still has opening positions, this means that the buy - sell must be more than 0.

b) Cell A16 (current formula is =UNIQUE(FILTER($B$3:$B$10, NOT(ISBLANK($B$3:$B$10)))) )

c) expected result is in cell F15:H19

This is correct because LON:AVV has buy of 8 and sell of 8 hence the quantity is 0 therefore there's no outstanding trades and should not be listed in the summary sheet.

This is what I want to achieve:

=UNIQUE(FILTER($B$3:$B$10, NOT(ISBLANK($B$3:$B$10)),SUM(FILTER($D$3:$D$10,$B$3:$B$10=A16,$A$3:$A$10="Buy")) - IFERROR(SUM(FILTER($D$3:$D$10,$B$3:$B$10=A16, ($A$3:$A$10="Sell")))) > 0 ))

Getting the difference of buy and sell for each unique epic and to ensure it's more than 0.

However, you will get a circular dependency error, which is understandable.

Is there a way where you could get only unique epic, then loop through each of those epic to make sure they have > 0 quantity (opening position)?

-------------------------------------------------------------------------------------------------------------------------------------------------------