Consolidate Multiple Ranges In #Excel

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””

cmr1

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:

cmr2

Choose “I will create the page fields” and click Next:

cmr3

Click in the Range field, highlight your data, click Add, then click Next:

cmr4

Decide where you choose to locate the PivotTable. I chose New worksheet:

cmr5

This will generate a PivotTable on a new worksheet. Next, double click on the Grand Total:

cmr6

This will create another new worksheet with the data in a table:

cmr7

Sort the Column with the headers in A to Z order:

cmr8

Next, concatenate the Column & Value columns using the “&” symbol. I also added a space to separate them:

cmr9

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:

cmr10

Perfect! Now, what do we do if the columns have a different number of values in them:

cmr11

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:

cmr12

Click on the filter arrow at the top and uncheck the Blanks box:

cmr13

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:

cmr14

Again, paste them using Paste Values Only and your list is complete:

cmr15

If you get stuck, watch the video and follow along.

Good luck and Happy Excelling!

Related Post

Free Download!

Subscribe to Download Your FREE Copy of
"My 70+ Favorite Excel Keyboard Shortcuts" Today!

, , ,

No comments yet.

Leave a Reply