Hello,
I would need help with the following table, I will try to explain the clearest way possible, because atm I cannot post an actual example.
So I have a list of clients (col A, client account number), type of orders (col B: buy or sell values), product name (col C: text) and volume (col D: numbers).
The client account number appears as many times as that client made an order and evidently products are repeated as well, as many products were purchased by different clients.m or same product was bought and sold by the same client several times.
Small example with one client, but imagine this is with thousands of clients and orders and products.
0001 buy Apple 3
0001 sell Apple 1
0001 sell Apple 1
0001 buy Pear 1
0001 buy Pear 1
0001 sell Pear 2
0001 buy Cherry 5
Now, I am making some other calculations based on these values in the next column E and I want to ignore from the calculations the lowest side of the order, e.g:
Client 0001 bought 3 Apples and sold 2, so I want to return 0 in column E for all the sell orders of Apples of the client 0001 (so in E2 and E3 I want 0 and <calculations> in E1, because is a buy order).
For Pears, the client 0001 bought 2 and sold 2, so buy and sell side is the same, so I want to calculate just for the buy orders, for the sell I want 0.
For Cherries, <calculate>, since there are no sell orders.
I tried to make a pivot table so I can have the orders aggregated per client, product and get the total sum of buy and sell orders, so I can see which side is higher, but then I do not know how to ignore those orders in my calculations in the original table, colum E.
I was thinking that (without pivot table), to calculate in my original table, column E, which is the highest, with sumif (something like: if the order type is sell and sumif(sell for this client, this product) < sumif(buy for this client, this product), then 0; otherwise <calculate>; otherwise (if is buy), then... the same, but for buy and with <=, to get the equal scenarios as well.
This table is very big, so it would make a lot of calculations this way, do you have a more efficient solution?
Thank you in advance and hope my explanation was good enough.
