Macros and VBA

[mmtawali]

How can I come up with a vba code to run the attached.

[#2]

@mmtawali Why VBA? Use a pivot table in stead. Name in the Row Field, Date in the Column field and Score in the Value filed. Set the calculation of "Sum of Score" to show value as a "Running Total in Date".

See attached.

[#3]

Thanks @Riny_van_Eekelen. I would also like to be adding a certain percentage to the running totals as an accumulation (Coumpounding). So the opening values plus a % interest and add that months amount son on and so forth. Apologies for incomplete infor. see attached.

[#4]

@mmtawali So you are not talking about Scores but Amounts owed, or something similar. What logic/system sits behind the percentages? Starting with 10% the first 4 days, then three days at 15%, and then down to 8%. and after that?

Better to upload a file that contains data that truly represents the data you are working with in real life. Remove any private or confidential information, though. 

[#5]

@Riny_van_Eekelen please see attached. That was a good point, should have provided a complete data and calculations.

[#6]

@mmtawali Thank you. But now I have follow-up question. What is it that you had in mind with VBA, to begin with? The SUMIFS formula seems to work perfect for what you want to achieve.

[#7]

Yeah its working, but i have a huge data to run this so am afraid that the excel keeps on jamming due to data size. Beacause it is supposed to do this for about 30k members and 5 years on daily transactions.

Hence looking for the best way on how i can run this type of calculations.

[#8 · Accepted solution]

@mmtawali Now I get you. Then PowerQuery could be the tool to help you, though it's not very straight-forward, due to the fact that not every person has a transaction on each day, but you want to accrue the rates on a daily basis on the running totals per person. The attached file contains several queries that ultimately come up with the answers as you included on the summary sheet.

Not sure though how this file will perform with 30K names and 5 years of transactions, as you will be looking at a potential of 55 million records to begin with, if I calculated correctly. Or can you split yur data into smaller groups? Perhaps it can be optimized but not by me. Sorry!

[#9]

@Riny_van_Eekelen, Thanks very much. I will try the power querry route.

You have been helpful.