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.

Days	Amt 1	Amt 2	Amt 3	Total	Example
Monday	1000	2000	500	2000	500 + 500 + 500 + 500
Monday	1000	2000	500	2000	500 + 500 + 500 + 500
Monday	1000	2000	500	2000	500 + 500 + 500 + 500
Monday	1000	2000	500	2000	500 + 500 + 500 + 500
Monday	1000	2000	250	1000	500 + 250 + 250
Monday	1000	2000	250	1000	500 + 250 + 250
Monday	1000	2000	500	1000	500 + 250 + 250
Tuesday	2900	1100	750	2900	750 + 750 + 500 + 450 +450
Tuesday	2900	1100	750	2900	750 + 750 + 500 + 450 +450
Tuesday	2900	1100	600	1100	600 + 500
Tuesday	2900	1100	500	1100	600 + 500
Tuesday	2900	1100	500	2900	750 + 750 + 500 + 450 +450
Tuesday	2900	1100	450	2900	750 + 750 + 500 + 450 +450
Tuesday	2900	1100	450	2900	750 + 750 + 500 + 450 +450
Wednesday	1500	2500	750	1500	750 + 750
Wednesday	1500	2500	750	1500	750 + 750
Wednesday	1500	2500	1000	2500	1000 + 1000 + 250 +250
Wednesday	1500	2500	1000	2500	1000 + 1000 + 250 +250
Wednesday	1500	2500	250	2500	1000 + 1000 + 250 +250
Wednesday	1500	2500	250	2500	1000 + 1000 + 250 +250
Thursday	2250	2750	1000	2250	1000 + 250 +1000
Thursday	2250	2750	750	2750	750 + 2000
Thursday	2250	2750	250	2250	1000 + 250 +1000
Thursday	2250	2750	2000	2750	750 + 2000
Thursday	2250	2750	1000	2250	1000 + 250 +1000
Friday	8450	1550	450	8450	450 + 2000 + 3000 + 1000 + 2000
Friday	8450	1550	550	1550	550 + 1000
Friday	8450	1550	2000	8450	450 + 2000 + 3000 + 1000 + 2000
Friday	8450	1550	3000	8450	450 + 2000 + 3000 + 1000 + 2000
Friday	8450	1550	1000	1550	550 + 1000
Friday	8450	1550	1000	8450	450 + 2000 + 3000 + 1000 + 2000
Friday	8450	1550	2000	8450	450 + 2000 + 3000 + 1000 + 2000

<colgroup><col><col span="4"><col></colgroup><tbody>
</tbody>

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.

Days	Cash in Bank	Cash in Hand	Cash Transactions
Monday	1100	900	750
Monday	1100	900	150
Monday	1100	900	800
Monday	1100	900	300
Tuesday	450	550	200
Tuesday	450	550	250
Tuesday	450	550	325
Tuesday	450	550	225
Wednesday	385	115	150
Wednesday	385	115	50
Wednesday	385	115	115
Wednesday	385	115	185

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>

Thanks in advance