formula help!

[Happyking]

I have column A with sales channel ID, column B with seller IDs (not in order, but with some duplicate, because a seller can sell his product via different channels). Column C with seller's original volume. Column D with the delivered volume of the most efficient channel (only one channel have a non-zero volume).

Now I want to create colomn E to show the remaining volume of each seller. I want to look up the ID of the seller that delivered volume (the non-zero volume in column D and deduct the delivered volume from that seller.

I can't just use E=C-D because then it won't pick up the correct volume for channel 4 and 7. I can't use pivot table because this is part of an optimization model and i need the calculation to flow through automatically. Thanks!!

A B C D E
1 5 100 0
2 3 20 0
3 7 500 3
4 7 500 0
5 1 90 0
6 2 30 0
7 7 500 0
8 4 200 0

 

[Matty]

Could you state the values you expect the formula to deliver?

Matty

 

[indiantrix]

I'm guessing you are asking for these results. I'm not clear on it, so please advise. I gathered that you wanted the same total result (ie, 1500 minus the "3") for all occurrences of that Vendor ID.
Larry.

Book2
	A	B	C	D	E
1	1	5	100	0	100
2	2	3	20	0	20
3	3	7	500	3	1497
4	4	7	500	0	1497
5	5	1	90	0	90
6	6	2	30	0	30
7	7	7	500	0	1497
8	8	4	200	0	200
Sheet1

 

[Matty]

I also presumed the same, Larry, but it'd be good if Happyking could clarify.

Matty