This week at the Data School we learned about Batch Macros. These can be used to union sheets of data that don’t easily come through in a wildcard union. With this knowledge I decided to revisit one of my first presentations at the Data School where I tried to merge 23 tables from Basketball Reference.

While a wildcard union seemed like the optimal choice, it didn’t merge all of the tables as the number of fields would change was not consistent throughout all tables.

My solution back then was to union all 23 tables together with the union tool. This gives me my desired result…

But it does not look pretty at all and is very time consuming.

Revisiting it, I’ve made a batch macro that will output a table, rename the fields to the first row of data and then move on to the next one as shown:

Then in order to make sure the columns lined up, I made sure it would auto configure by name.

And there, the desired result without going through a convoluted process.

Ethan Tai
Author: Ethan Tai