subtracting 2 columns of data from each other when labels in separate columns match

Hi Thanks for your help with this.

the results are very close to what I would expect, but seem to go awry where the cells are not on the same row...

I will have a look at this again and get back to you with any progress.



Jim

Hi JBeaucaire

That formula works perfectly! Thank you so much for helping me to resolve this.


Now, to complicate matters further, can anyone suggest a way I can amend the formula to also flag up any items that are in column C but not in column A?

Just to explain. We need to do this as the larger dataset represents sales made, whilst the smaller represents sales paid on so not only do we need to check that the figures match (as arranged by your formula), but we also need to check that we have been paid on all items (as per this requested amendment).

Many thanks,


Jim

Perfect, once again, my thanks for all your help with this.

Bump. I am trying to use this exact same formula. I have 4 colums.

S=ID T= Value, U=ID, V=Value

I need to create a forumula that if U=S then subtract V from T.

I am presently using the forumla: =IF(ISERROR(MATCH(U3,$S$3:$S$2972,0)),"",T3-INDEX($V$3:$V$2972,MATCH(U3,$S$3:$S$2972,0)))

and starting at places where a U does not = a S then it gives me random numbers.

To clarify this, there are 2972 S's, and only 2112 U's. So What I need my forumula to do is where there is not a coresponding S then just move to the next calculation. I need a Value for every U all the way down.

Sample Data:

S T U V

470 4169 10,548 470 4169 4,015
470 4127 10,828 470 4127 9,554
470 5474 118,461 470 5474 117,392
470 4830 2,473 470 4830 942
470 4170 10,548 470 4170 4,015
470 4196 79,019 470 4196 31,428
470 15034 788,488 470 4143 1,178
470 4143 3,096 470 4214 5,849
470 4214 14,706 470 4144 2,356

There will be a value for every S, and when there is a value for every S I need this formula to find the corresponding value in V then subtract that the value of the corresponding T. S and U are product id's and T and V are volumes, I just need to know what volumes changed.

Hello Black Car 10, and welcome to the forum.

Unfortunately you have inadvertently broken one of the forum rules. Please read the following and make the necessary change. Thanks.

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.