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 a link to the code above so you can review it side-by-side with the walk through below. Right-click, “Open in new window”.
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
dicCabins <- this
Dictionary holds all the cabin names as
Dictionary objects are what we’ll loop through in the next step,
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.
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!
Did this script and transform work for you? If not, let me know and I’ll help you get what you need! And if you’d like more step-by-step, no-bullshit VBA guides delivered direct to your inbox, join my email newsletter below.
Get the VBA Toolbelt!
Quit digging through old projects and forums like a chump! Download the VBA Toolbelt and start with the most common Excel tasks already done for you.