• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to sort data with condition (exclude item with zero inventory)?

Simca

New Member
Hi, I use excel spreadsheet to keep track of my stock position.
I use sort(unique) function to list out all the stock in my stock buy/sell transaction.
So it will automatically list out all the stock that I have buy/sell in the transaction.
But I want to exclude the stock that I have 0 position in my portfolio.
For example,
If you look at stock buy/sell transaction in my excel file, I buy 2 shares of ICE, buy 1 share of BLK, sell 1 share of BLK.
So the open position should be only ICE.
When I use sort(unique) function, it will list out both ICE and BLK.
I want to exclude BLK from the list since I have no more position.
Is there any solution to it?
Thanks in advance.
 

Attachments

  • Example.xlsx
    10.2 KB · Views: 7
SUMIFS would allow you to calculate the final position for each security before you use UNIQUE. FILTER will remove the unwanted item.
Code:
= LET(
    finalQty,  SUMIFS(Quantity, Security, Security),
    stockHeld, FILTER(Security, finalQty>0),
    SORT(UNIQUE(stockHeld))
  )
I have also included a version of the formula that returns the final holding as well as the security name.
Code:
= LET(
    finalQty,  SUMIFS(Quantity, Security, Security),
    stockList, FILTER(Security, finalQty>0),
    distinct,  SORT(UNIQUE(stockList)),
    holding,   XLOOKUP(distinct, Security, finalQty),
    HSTACK(distinct, holding)
  )
The formula just gets that much longer.
 

Attachments

  • Example (1).xlsx
    12.2 KB · Views: 3
Back
Top