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.

Want to join VBA traning?

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.

Now, we need to transform this data in to summary, I have developed a code in the file which you can download by following the link below. this is very simple code and can create this summary faster than a blink of an eye.

Download File

Join my VBA whatsApp group to know more

Subscribe to get news from IT Chat

0 replies

Leave a Reply

Want to join the discussion?
Feel free to contribute!

Leave a Reply

Your email address will not be published. Required fields are marked *