Combining Data and Transposing the Results to Rows not Columns with VBA
What should you do when you need you need to combine data but write the output into rows, not columns?
99 times out of 100, a pivot table is the answer to your data aggregation needs. But what about getting your output into row format — and, more specifically, what if you need each item in a new column? Consider a list of camper names and their assigned cabins — what if you need cabins on rows, with each assigned camper in a new column?
That’s a lot harder than just dropping in a pivot table, but with a little VBA we can make it happen.
First things first — the code below relies on the Dictionary object, when is not part of the standard VBA suite! Don’t let that scare you though, as you can add it with a couple clicks.
From the VBA window, click Tools > References..., then check the box next to “Microsoft Scripting Runtime” and you’re done. Here’s a gif of showing you how to do that:
Now that you’ve enabled this library, the code below will create a new “cabins” sheet with each camper in his or her own column.
Here’s the script above in action:
Let’s walk through this macro in the context of our trusty 4-step VBA process:
Step 1 – Setup
Step 2 – Exploration
Step 3 – Execution
Step 4 – Cleanup
Lines 4-19 handles Step 1 – Setup. We’re dimensioning our variables, assigning worksheets and creating a new worksheet named “cabins”. (Note: if you already have a “cabins” sheet, this macro will fail — so make sure you delete that sheet if you want to re-run the code!)
Lines 22-43 is our Step 2 – Exploration, which is usually more in-depth than the Setup. First, we need to identify our data “limit”, which is the last occupied row. We assign that value to lngLastRow, then run a For loop over the campers and cabins, populating a Dictionary object for each.
dicCampers <- this Dictionary holds all the camper names as Keys, with the camper’s cabin as Items
dicCabins <- this Dictionary holds all the cabin names as Keys AND Items
These Dictionary objects are what we’ll loop through in the next step, Execution.
Lines 46-67 handle our Step 3 – Execution. There are two loops running here, which can be confusing, so let’s get into the nitty gritties. Here’s the outer loop:
For Each varCabin In dicCabins.Keys
dicCabins is a Dictionary containing the unique cabin names, so it’s small. In the gif above, there are only 5 cabins: “Singers”, “Guitarists”, “Bassists”, “Drummers” and “Pianists”. Each of these cabin names belongs on its own row, so we increment the lngCabinRow counter by one each time this loop completes.
The inner loop is bigger:
For Each varCamper In dicCampers.Keys
dicCampers is a Dictionary containing all the camper names (as Keys), with each camper’s cabin stored as the Item. Here, the loop check’s each camper’s cabin — if the cabin matches active cabin from dicCabins, then we write that camper into the row.
Phew!
Finally, line 70 is our super-simple Step 4 – Cleanup, where we notify the user that the script has completed.
With that, you’ve got your campers listed in columns, with a row for each cabin. Nice!