Nov 19 2021 12:30 AM
How can I come up with a vba code to run the attached.
Nov 19 2021 12:57 AM - edited Nov 19 2021 12:58 AM
@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.
Nov 19 2021 01:10 AM
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.
Nov 19 2021 01:19 AM
@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.
Nov 19 2021 01:45 AM
@Riny_van_Eekelen please see attached. That was a good point, should have provided a complete data and calculations.
Nov 19 2021 01:54 AM
@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.
Nov 19 2021 02:02 AM
Nov 19 2021 04:41 AM
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!
Nov 19 2021 04:51 AM
@Riny_van_Eekelen, Thanks very much. I will try the power querry route.
You have been helpful.
Nov 19 2021 04:41 AM
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!