VBA macro to unpivot data in Excel for database table format
Often I have data in Excel that needs to be converted to a database table-friendly format. The input received is that of a pivot table or a cross tab, but I need to transpose the data to match the structure of my database table.
While there is a built-in way in Excel to unpivot data, I find using a macro (VBA code) faster to use.
Demo
Let’s assume we have a dataset that shows how many times an employee viewed a report by month. There are two static columns (Employee Name + Report Name) and then several dynamic columns (one for each Month).
After running the macro, this new format is compatible with a relational database table, and can easily be imported in.
The Code for the Macro
Insert a new code module in Excel, then paste in the code below:
On Windows, hit Alt + F11 to get to the Microsoft Visual Basic for Applications window. Then Insert >> Module.
Code Explanation
This is written as a subroutine, but I also had a use-case where I needed to call it as a function, passing in the desired worksheet names. For that, use the code in Line 4 in place of Line 1.
First, I ask the user “how many static columns are there?” In the example above, there were two (Employee Name and Report Name). It may not always have two columns, so I explicitly ask the user how many there are.
The code will create a new worksheet (I’ve named it Output on Line 9). It will then fill in the static column header names, based on the user’s input of the previous question.
My data is often time-phased; i.e. there is a specific value by month. To avoid issues with reserved words, I’ll be creating two new column headers called the_month and the_value.
The heart of the macro is the loop on rows 47 to 80. Starting at the top of the Excel file, I go down until there’s a blank row. But each row of the spreadsheet (Lines 53 to 77) has a loop for:
- “Static” columns (i.e. unchanging) is done on Lines 56 to 65. Here it’s the Employee Name + Report Name.
- “Dynamic” columns (i.e. number of column that we don’t know how many there will be) is done on lines 68 to 73. For this example, we are looping through months; maybe there’s only six months, but maybe there were twelve. We need to dynamically loop over the unknown number of columns that there are. This code will place values in the columns for the_month and the_value.
I’ve commented out some optional formatting in lines 84 to 96. Depending on the type of data, you may want to format it (ex. if it’s a date, you could use m/d/yyyy; or if it was a number, make it two decimal places).
Finally, I “freeze” the top row, and set the cursor to Cell A1.
Again, there are other ways to accomplish this (ex. built-in methods in Excel), but I find creating a macro-enabled file with this code the fastest way to convert unpivoted data from a spreadsheet into a database table-friendly format.
