How to add 2 cell values and match with another cell value

i have attached an excel for the reference, kindly help me with the solution for below query.

excel have total 5 col’s - Trade Ref no, Operation type, Quantity,Amount,Local Code.
we will filter the data with Operation Type - Buy & Sell (col B).

  • here in the attached excel, row 5 had Buy operation type and have quantity of 14165 (C5).
  • now bot should go to add every 2 value of Quantity Col values (col C) Corresponding to Sell Operation Type (Col B) only to match with C5 value as below
    = C2+C3 , if not matched then
    = C2 + C4 … C2+C6 … C3+C4…C3+C6 similarly in all possible addition of 2 values to match with C5 value.

Attached excel for reference.

Test file matching data.xlsx (11.2 KB)

Thanks in advance,
Vighnaraj C S

Hi @Vighnaraj ,
See somethings am confused okay rows with buy can be taken but with what are you comparing?
Are you comparing the values of “Quaqntity” column or “Trade Ref No” column?
And after getting a match what do you mean by “now we need to add other Sell quantity values 2 at a time to match C5.”

This all points are still unclear please explain it more clearly so that we can help you in the best possible way.

Thanks & Regards,
Shubham Dutta

HI Shubham,
i have updated the query kindly check.

Reg,
Vighnaraj

Hi @Vighnaraj ,
Thanks for the explanation. Yes this can be made possible I will send you the sample code.
One doubt I have still after successful match what you are doing?
For example if C3+C4=C5 then what?

if in the case C3 + C4 = C5 update the Status in col F5 as matched.
then go for the next filtered Buy operation type value in C7 and do the same checks for matching.

Hi @Vighnaraj ,
I have developed the code and sending it to you for your reference please do have a look at it.
It also will reflect output as “Matched” in excel file.
New folder (3).zip (11.8 KB)

Thanks & Regards,
Shubham Dutta

Hi Shubham_Dutta,

Thank you for the code shared. and it worked.
do you have any suggestion to do the same process using regression method.
like matching Buy quantity with adding multiple sell quantity for all the available matches to check.

thank you and regards,
Vighnaraj C S

Hi @Vighnaraj ,
so you want to add C2 with C6 or C7 to make it equal to C5 rather than going in a sequence of C2+C3 or C3+C4 or C6+C7?

Hi Shubham,

Yes, here in the shared excel it had 2 Buy operation type. so i need to check all the possible additions of Sell Quantity values to match both Buy quantity values.

its like one to one or one to many matching.

thanks,
Vighnaraj C S

Hi @Vighnaraj ,
that can be done just you have to subtract each “sell” values with a “buy” value and see whether the result value is present in any one of the cell value, if it is there hence match found else no match.
For example:
Buy value: 14615- first sell value:7000= 7615 now see whether this result value matches with any sell values present if yes matched else no match.
Similarly, Buy value: 14615- second sell value:7615= 7000 now see whether this result value matches with any sell values present if yes matched else no match.

I hope you understood the solution.

Thanks & Regards,
Shubham Dutta