VBA Code for creating a formula for adding specific values (=IF(A2:A10)=B2,B2,C2))

[Feroz90]

Hi All,

In the below example, I want to add each days splitted amount in Amt 3 and reconcile with Amt 1 & Amt 2, say for example, For Monday, if we add first 4 rows in Amt 3, we get 2000 which is in Amt 2 and if we add next 3 rows of Monday , we get 1000 which is in Amt 1. Anyone's help with any VBA code will be appreciated.

[TABLE="width: 542"]
<colgroup><col><col span="4"><col></colgroup><tbody>[TR]
[TD]Days[/TD]
[TD]Amt 1[/TD]
[TD]Amt 2[/TD]
[TD]Amt 3[/TD]
[TD]Total[/TD]
[TD]Example[/TD]
[/TR]
[TR]
[TD]Monday[/TD]
[TD]1000[/TD]
[TD]2000[/TD]
[TD]500[/TD]
[TD]2000[/TD]
[TD]500 + 500 + 500 + 500[/TD]
[/TR]
[TR]
[TD]Monday[/TD]
[TD]1000[/TD]
[TD]2000[/TD]
[TD]500[/TD]
[TD]2000[/TD]
[TD]500 + 500 + 500 + 500[/TD]
[/TR]
[TR]
[TD]Monday[/TD]
[TD]1000[/TD]
[TD]2000[/TD]
[TD]500[/TD]
[TD]2000[/TD]
[TD]500 + 500 + 500 + 500[/TD]
[/TR]
[TR]
[TD]Monday[/TD]
[TD]1000[/TD]
[TD]2000[/TD]
[TD]500[/TD]
[TD]2000[/TD]
[TD]500 + 500 + 500 + 500[/TD]
[/TR]
[TR]
[TD]Monday[/TD]
[TD]1000[/TD]
[TD]2000[/TD]
[TD]250[/TD]
[TD]1000[/TD]
[TD]500 + 250 + 250[/TD]
[/TR]
[TR]
[TD]Monday[/TD]
[TD]1000[/TD]
[TD]2000[/TD]
[TD]250[/TD]
[TD]1000[/TD]
[TD]500 + 250 + 250[/TD]
[/TR]
[TR]
[TD]Monday[/TD]
[TD]1000[/TD]
[TD]2000[/TD]
[TD]500[/TD]
[TD]1000[/TD]
[TD]500 + 250 + 250[/TD]
[/TR]
[TR]
[TD]Tuesday[/TD]
[TD]2900[/TD]
[TD]1100[/TD]
[TD]750[/TD]
[TD]2900[/TD]
[TD]750 + 750 + 500 + 450 +450[/TD]
[/TR]
[TR]
[TD]Tuesday[/TD]
[TD]2900[/TD]
[TD]1100[/TD]
[TD]750[/TD]
[TD]2900[/TD]
[TD]750 + 750 + 500 + 450 +450[/TD]
[/TR]
[TR]
[TD]Tuesday[/TD]
[TD]2900[/TD]
[TD]1100[/TD]
[TD]600[/TD]
[TD]1100[/TD]
[TD]600 + 500[/TD]
[/TR]
[TR]
[TD]Tuesday[/TD]
[TD]2900[/TD]
[TD]1100[/TD]
[TD]500[/TD]
[TD]1100[/TD]
[TD]600 + 500[/TD]
[/TR]
[TR]
[TD]Tuesday[/TD]
[TD]2900[/TD]
[TD]1100[/TD]
[TD]500[/TD]
[TD]2900[/TD]
[TD]750 + 750 + 500 + 450 +450[/TD]
[/TR]
[TR]
[TD]Tuesday[/TD]
[TD]2900[/TD]
[TD]1100[/TD]
[TD]450[/TD]
[TD]2900[/TD]
[TD]750 + 750 + 500 + 450 +450[/TD]
[/TR]
[TR]
[TD]Tuesday[/TD]
[TD]2900[/TD]
[TD]1100[/TD]
[TD]450[/TD]
[TD]2900[/TD]
[TD]750 + 750 + 500 + 450 +450[/TD]
[/TR]
[TR]
[TD]Wednesday[/TD]
[TD]1500[/TD]
[TD]2500[/TD]
[TD]750[/TD]
[TD]1500[/TD]
[TD]750 + 750[/TD]
[/TR]
[TR]
[TD]Wednesday[/TD]
[TD]1500[/TD]
[TD]2500[/TD]
[TD]750[/TD]
[TD]1500[/TD]
[TD]750 + 750[/TD]
[/TR]
[TR]
[TD]Wednesday[/TD]
[TD]1500[/TD]
[TD]2500[/TD]
[TD]1000[/TD]
[TD]2500[/TD]
[TD]1000 + 1000 + 250 +250[/TD]
[/TR]
[TR]
[TD]Wednesday[/TD]
[TD]1500[/TD]
[TD]2500[/TD]
[TD]1000[/TD]
[TD]2500[/TD]
[TD]1000 + 1000 + 250 +250[/TD]
[/TR]
[TR]
[TD]Wednesday[/TD]
[TD]1500[/TD]
[TD]2500[/TD]
[TD]250[/TD]
[TD]2500[/TD]
[TD]1000 + 1000 + 250 +250[/TD]
[/TR]
[TR]
[TD]Wednesday[/TD]
[TD]1500[/TD]
[TD]2500[/TD]
[TD]250[/TD]
[TD]2500[/TD]
[TD]1000 + 1000 + 250 +250[/TD]
[/TR]
[TR]
[TD]Thursday[/TD]
[TD]2250[/TD]
[TD]2750[/TD]
[TD]1000[/TD]
[TD]2250[/TD]
[TD]1000 + 250 +1000[/TD]
[/TR]
[TR]
[TD]Thursday[/TD]
[TD]2250[/TD]
[TD]2750[/TD]
[TD]750[/TD]
[TD]2750[/TD]
[TD]750 + 2000[/TD]
[/TR]
[TR]
[TD]Thursday[/TD]
[TD]2250[/TD]
[TD]2750[/TD]
[TD]250[/TD]
[TD]2250[/TD]
[TD]1000 + 250 +1000[/TD]
[/TR]
[TR]
[TD]Thursday[/TD]
[TD]2250[/TD]
[TD]2750[/TD]
[TD]2000[/TD]
[TD]2750[/TD]
[TD]750 + 2000[/TD]
[/TR]
[TR]
[TD]Thursday[/TD]
[TD]2250[/TD]
[TD]2750[/TD]
[TD]1000[/TD]
[TD]2250[/TD]
[TD]1000 + 250 +1000[/TD]
[/TR]
[TR]
[TD]Friday[/TD]
[TD]8450[/TD]
[TD]1550[/TD]
[TD]450[/TD]
[TD]8450[/TD]
[TD]450 + 2000 + 3000 + 1000 + 2000[/TD]
[/TR]
[TR]
[TD]Friday[/TD]
[TD]8450[/TD]
[TD]1550[/TD]
[TD]550[/TD]
[TD]1550[/TD]
[TD]550 + 1000[/TD]
[/TR]
[TR]
[TD]Friday[/TD]
[TD]8450[/TD]
[TD]1550[/TD]
[TD]2000[/TD]
[TD]8450[/TD]
[TD]450 + 2000 + 3000 + 1000 + 2000[/TD]
[/TR]
[TR]
[TD]Friday[/TD]
[TD]8450[/TD]
[TD]1550[/TD]
[TD]3000[/TD]
[TD]8450[/TD]
[TD]450 + 2000 + 3000 + 1000 + 2000[/TD]
[/TR]
[TR]
[TD]Friday[/TD]
[TD]8450[/TD]
[TD]1550[/TD]
[TD]1000[/TD]
[TD]1550[/TD]
[TD]550 + 1000[/TD]
[/TR]
[TR]
[TD]Friday[/TD]
[TD]8450[/TD]
[TD]1550[/TD]
[TD]1000[/TD]
[TD]8450[/TD]
[TD]450 + 2000 + 3000 + 1000 + 2000[/TD]
[/TR]
[TR]
[TD]Friday[/TD]
[TD]8450[/TD]
[TD]1550[/TD]
[TD]2000[/TD]
[TD]8450[/TD]
[TD]450 + 2000 + 3000 + 1000 + 2000[/TD]
[/TR]
</tbody>[/TABLE]

Thanks you in advance

 

[Joe4]

Have you tried using Excel Solver?
That is the only non-VBA method I am aware of.
If you are unsure on how to use it, just Google it. There are tons of tutorials and demos to be found.

 

[Feroz90]

Nope, we can able to do it for only one solution, not for list of numbers. That is why I need to create a new one.

 

[Joe4]

When you say "list of numbers", are you referring to the various values in column D that add up to the value in the first row of column C, or are you referring to something else?

 

[Feroz90]

No, We have the list like Monday, We have various days to reconcile.

 

[Joe4]

It sounds like you are talking about something like multiple simultaneous "Solver-like" sitations. I do not know of any "quick and easy" ways of doing that. I think you may be wading into some pretty complex VBA code, one that it might not be reasonable to expect to be solved via free help. You may have to enlist the help of some Consulting Services (https://www.mrexcel.com/consulting-services/).

 

[pbornemeier]

Re: VBA Code for adding specific values in two columns

There does not seem to be consistency in how rows are combined to make their column 3 values add to give values in columns 1 & 2.
Please recreate your example using unique numbers in column 3.
What is the rule for determining which rows are combined to create the values in column 1
What is the rule for determining which rows are combined to create the values in column 2

 

[Joe4]

Re: VBA Code for adding specific values in two columns

I have merged your two threads together, since they were on the same question.

In the future, please do not post the same question multiple times. All clarifications, follow-ups, and bumps should be posted back to the original thread. Per forum rules, posts of a duplicate nature will be locked or deleted (rule 12 here: Forum Rules).

 

[Feroz90]

Re: VBA Code for adding specific values in two columns

Hi,

Thank you for your response.

Please find below the example, each day have different cash transactions, but every day's cash balance are the same, so I just want to know which are all transactions are debited and which are all credited.

[TABLE="width: 415"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]Days[/TD]
[TD]Cash in Bank[/TD]
[TD]Cash in Hand[/TD]
[TD]Cash Transactions[/TD]
[/TR]
[TR]
[TD]Monday[/TD]
[TD]1100[/TD]
[TD]900[/TD]
[TD]750[/TD]
[/TR]
[TR]
[TD]Monday[/TD]
[TD]1100[/TD]
[TD]900[/TD]
[TD]150[/TD]
[/TR]
[TR]
[TD]Monday[/TD]
[TD]1100[/TD]
[TD]900[/TD]
[TD]800[/TD]
[/TR]
[TR]
[TD]Monday[/TD]
[TD]1100[/TD]
[TD]900[/TD]
[TD]300[/TD]
[/TR]
[TR]
[TD]Tuesday[/TD]
[TD]450[/TD]
[TD]550[/TD]
[TD]200[/TD]
[/TR]
[TR]
[TD]Tuesday[/TD]
[TD]450[/TD]
[TD]550[/TD]
[TD]250[/TD]
[/TR]
[TR]
[TD]Tuesday[/TD]
[TD]450[/TD]
[TD]550[/TD]
[TD]325[/TD]
[/TR]
[TR]
[TD]Tuesday[/TD]
[TD]450[/TD]
[TD]550[/TD]
[TD]225[/TD]
[/TR]
[TR]
[TD]Wednesday[/TD]
[TD]385[/TD]
[TD]115[/TD]
[TD]150[/TD]
[/TR]
[TR]
[TD]Wednesday[/TD]
[TD]385[/TD]
[TD]115[/TD]
[TD]50[/TD]
[/TR]
[TR]
[TD]Wednesday[/TD]
[TD]385[/TD]
[TD]115[/TD]
[TD]115[/TD]
[/TR]
[TR]
[TD]Wednesday[/TD]
[TD]385[/TD]
[TD]115[/TD]
[TD]185

[/TD]
[/TR]
</tbody>[/TABLE]

Thanks in advance