Excelllllllllllllllllllll
Board Regular
- Joined
- Jun 13, 2017
- Messages
- 108
Greetings!
I've recently been given a sizable chunk of my company's finance sheets to go through the processed payments only (bought X qty - sold X qty)
I can do this manually, but am having a trouble creating a formula or pivot that can help me with this.
Basically I need a way to calculate the Purchase from row 1+ sale from row 2. Then ignore rows 3&6 completely until we sell 57 units (or 56 vs 56....and 1 vs 1 - because we buy each order separately so we wouldn't sell less than we bough, but we may sell all that we hold if it's for the same client (but this is only by request so it doesn't happen all the time)).
And then I need to do the same for Company 2.
SO - I need a formula or pivot for the above example that shows:
Company 1 = 60
Company 2 = 600
I've recently been given a sizable chunk of my company's finance sheets to go through the processed payments only (bought X qty - sold X qty)
I can do this manually, but am having a trouble creating a formula or pivot that can help me with this.
COLUMN D | COLUMN E | COLUMN F | COLUMN R | COLUMN S |
Client | Transaction | Quantity | Order ID (always unique number) | Final Price (formula) |
Company1 | Purchase | 6 | J512f3151f1sfhg4t23r | -600 |
Company1 | Sale | -6 | OIHKLNhbu2g8ihor | 660 |
Company1 | Purchase | 56 | *(TH"OIGNEP6D | -5600 |
Company2 | Purchase | -12 | LGPNJWHEI4GHjdkn | 1200 |
Company2 | Sale | 12 | IPHEIL£n4kkrENG | 1800 |
Company1 | Purchase | 1 | IOH£*g23fgopj | 100 |
Basically I need a way to calculate the Purchase from row 1+ sale from row 2. Then ignore rows 3&6 completely until we sell 57 units (or 56 vs 56....and 1 vs 1 - because we buy each order separately so we wouldn't sell less than we bough, but we may sell all that we hold if it's for the same client (but this is only by request so it doesn't happen all the time)).
And then I need to do the same for Company 2.
SO - I need a formula or pivot for the above example that shows:
Company 1 = 60
Company 2 = 600