Forum Discussion
Macros and VBA
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!
Hence looking for the best way on how i can run this type of calculations.
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!
Riny_van_Eekelen, Thanks very much. I will try the power querry route.
You have been helpful.