In this post I will use the Consolidate Multiple Ranges option to concatenate table headers with their column values:
You can download the file here and follow along. If you get a preview, look for the download arrow in the upper right hand corner.
We have a table here and want to convert it to a list that concatenates each column heading with the data in that column, as shown in column “I””
I did a post recently that performed a similar task using 6 different functions. You can find that post here.
To accomplish this we are going to use the PivotTable and PivotChart Wizard. Oddly, Excel doesn’t put that on the ribbon, but there are two ways to access it:
1) Use the keyboard sequence Alt D P
2) Add the shortcut to your Quick Access Toolbar (QAT) by going to File => Options => Quick Access Toolbar. Choose “All Commands” from the drop down at the top of the left column. Search for “PivotTable and PivotChart Wizard” and add it to your QAT.
Open the Wizard dialog box, select Multiple consolidation ranges, and click Next:
Choose “I will create the page fields” and click Next:
Click in the Range field, highlight your data, click Add, then click Next:
Decide where you choose to locate the PivotTable. I chose New worksheet:
This will generate a PivotTable on a new worksheet. Next, double click on the Grand Total:
This will create another new worksheet with the data in a table:
Sort the Column with the headers in A to Z order:
Next, concatenate the Column & Value columns using the “&” symbol. I also added a space to separate them:
Highlight the concatenated column, copy it and paste it into the original sheet, using Paste Special Values. I’ve added another column to double check that the results are in the order I wanted:
Perfect! Now, what do we do if the columns have a different number of values in them:
The only difference in the process is that when the table is created on the new worksheet, it will have rows where the Values column is blank:
Click on the filter arrow at the top and uncheck the Blanks box:
Now, after you concatenate the columns, when you copy the data, choose Visible Cells Only. To do this, highlight your range, the go the Find & Select on the Home ribbon, click on Go To Special, and choose Visible Cells Only. You can also add this option to your ribbon or QAT as you did the Wizard earlier:
Again, paste them using Paste Values Only and your list is complete:
If you get stuck, watch the video and follow along.
Good luck and Happy Excelling!