VBA Dictionary – How to summarize data
Data summarization is an elementry step to analyse data and excel itself provides a great feature which is called pivot table. pivot table has great features which you can use to make your data tell you a story. but it takes memory to store data into pivot cache as a cube to faster operation and increase file size and thats where VBA Dictionary comes in to play.
Many time we don’t really need to have a pivot table we can use some sort of formulas and have our summary page refresh as soon as new data comes in. But again, formulas takes time to recalculate and if it is a large data formulas may hang your file.
Now, we have got a third method to summarize data without having those two issue and this is of course VBA. pretty and simple, you just need to write a chunk of code and it will transform your data into summary and then you can do further charts and all.
This is pretty simple example using VBA Dictionary. this table shows the data structure.