Today's Excel question from Rico: How to combine columns that have the same header? I first use Consolidate and then a formula solution using UNIQUE and SUMIFS. Although - I discover a new bug in Consolidate.
Transcript of the video:
Learn Excel from MrExcel Podcast Episode 2362.
Add columns that have the same header.
Hey, welcome back to the MrExcel netcast. I am Bill Jelen.
I know I am being set up here. This question is from Rico on YouTube.
How to group columns with the same header?
Here - we have two columns that have the header 2.
He wants to take zero plus one to get one.
One plus zero to get one.
And so on.
Here are my two ways.
The easy way.
This is old-school, going back to 1993.
Data, Consolidate. That is Alt+D N.
Specify the Reference including the headers.
And I say that I want to use the Labels only in the Top Row.
Click OK. It gives me 1, 2, 3, 4, 5.
It collapses them down.
And here where I have zero plus one, I get one.
One plus one, I get two.
This is fast and easy. The problem is: if this data changes, The Consolidate does not change.
You have to update it.
And, there is a new bug. There never used to be this bug.
If I come back in here with Alt+D, N.
And click OK, it only updates the first column.
So I have to clear it out and then Alt+D N.
And click OK.
[ Sigh ] [ Ugh ] Super annoying.
Here is a two formula solution.
The first formula is =UNIQUE(.
Of the headers here.
I will say that "by column" is True.
Click OK and I get the numbers 1 through 5.
And then it is straight-forward from there.
=SUMIFS(. What do we want to sum? The numbers in this row.
I am going to press F4 not just once.
I want to lock it down to the columns but not the rows.
The rows need to be able to move.
Press F4 three times.
For the criteria range, look through the header row.
In this case, I need that reference to stay in that row, so press F4 to put all the dollar signs in.
And then, I think I can do this all in one.
If I point to this cell. V4 with a dollar sign before the 4.
So I press F4 two times. And then type a Hash.
The hash indicates the whole array generated by the UNIQUE.
Close paren.
I get the answers for the first one and then copy it down.
If you are watching this, be sure to check the YouTube comments below.
Rico probably has an awesome single-formula solutions. Because that is what Rico likes to do.
Check out my book, MrExcel 2020 Seing Excel Clearly. Click the (i) in the top right corner.
If you want to know new Excel features first, click Subscribe down below the video.
I pride myself on being the first to show you new features in Excel.
Plus some Q&A like this.
Also, Like and Ring that bell.
Feel free to post any questions or comments in the Comments section below.
I want to thank Rico for sending that question in. I can't wait to see what answer he has.
And thank you for stopping by. We will see you next time for another netcast from MrExcel.
Add columns that have the same header.
Hey, welcome back to the MrExcel netcast. I am Bill Jelen.
I know I am being set up here. This question is from Rico on YouTube.
How to group columns with the same header?
Here - we have two columns that have the header 2.
He wants to take zero plus one to get one.
One plus zero to get one.
And so on.
Here are my two ways.
The easy way.
This is old-school, going back to 1993.
Data, Consolidate. That is Alt+D N.
Specify the Reference including the headers.
And I say that I want to use the Labels only in the Top Row.
Click OK. It gives me 1, 2, 3, 4, 5.
It collapses them down.
And here where I have zero plus one, I get one.
One plus one, I get two.
This is fast and easy. The problem is: if this data changes, The Consolidate does not change.
You have to update it.
And, there is a new bug. There never used to be this bug.
If I come back in here with Alt+D, N.
And click OK, it only updates the first column.
So I have to clear it out and then Alt+D N.
And click OK.
[ Sigh ] [ Ugh ] Super annoying.
Here is a two formula solution.
The first formula is =UNIQUE(.
Of the headers here.
I will say that "by column" is True.
Click OK and I get the numbers 1 through 5.
And then it is straight-forward from there.
=SUMIFS(. What do we want to sum? The numbers in this row.
I am going to press F4 not just once.
I want to lock it down to the columns but not the rows.
The rows need to be able to move.
Press F4 three times.
For the criteria range, look through the header row.
In this case, I need that reference to stay in that row, so press F4 to put all the dollar signs in.
And then, I think I can do this all in one.
If I point to this cell. V4 with a dollar sign before the 4.
So I press F4 two times. And then type a Hash.
The hash indicates the whole array generated by the UNIQUE.
Close paren.
I get the answers for the first one and then copy it down.
If you are watching this, be sure to check the YouTube comments below.
Rico probably has an awesome single-formula solutions. Because that is what Rico likes to do.
Check out my book, MrExcel 2020 Seing Excel Clearly. Click the (i) in the top right corner.
If you want to know new Excel features first, click Subscribe down below the video.
I pride myself on being the first to show you new features in Excel.
Plus some Q&A like this.
Also, Like and Ring that bell.
Feel free to post any questions or comments in the Comments section below.
I want to thank Rico for sending that question in. I can't wait to see what answer he has.
And thank you for stopping by. We will see you next time for another netcast from MrExcel.
