Best way to get rows by date in Excel (Pivot Table vs VBA)

Question

I have a relatively small amount of data in two Excel tables in different Worksheets (no more than 1000 rows each). These tables have the records for sales and expenses of an office, respectively. Each entry has a row indicating its date among other data.

What I need to do is to be able to physically print all the entries, from both tables, that belong to a specific date, like January 20th for example. In order to do this, I was thinking that the best way to achieve this was creating a new table that merged and filtered all the data according to my needs. However, when thinking about how to do it, I started considering two approaches and I'm not sure which one would be the best considering that the amount of data used will increase over time. I'm looking for the best solution in terms of speed and file size.

My first idea was to merge both tables using Power Query inside Excel, then create a Pivot Table from that and filter by date to only show the entries I'm interested in. One issue that I'm thinking of is that a single table could have around 1 million rows, but if I combine both (sales and expenses) I would need to check that the amount of rows between both doesn't exceed 1 million rows, unless there is a way to directly link the Power Query to the Pivot Table instead of creating a new one and using that as the source.

The second approach would be using a VBA macro to loop through both tables, and copy the rows based on an If condition to a new table. This would solve the 1 million row limit because only the rows of interest would be copied to the printing table.

I searched a while for any information about this but found nothing. I'm aware that Pivot Tables slow down with a lot of data, but I'm not sure if coding the thing in VBA would be even slower. I'd like to get some info to optimize this, something well know that I'm missing like it's always faster to use INDEX and MATCH functions instead of VLOOKUP to make your code faster. Also, if there is a better way to achieve this I would highly appreciate any suggestions.

question from:https://stackoverflow.com/questions/65891012/best-way-to-get-rows-by-date-in-excel-pivot-table-vs-vba

Answer 1

Waitting for answers