Kara asks: Do you have a trick for the subtotal function?
In a "subtotal by" task where I use three fields (ID / Name / Amount) to get the total amount, Excel makes me choose between ID or Name for "at each change in" but I'd like to retain both so I currently perform data gymnastics to concatenate the ID and Name fields.....and then after using the function, I perform more gymnastics after selecting level 2, "go to special" / visible cells only on the subtotals, paste to another sheet, and then split the ID and name back out with the subtotaled amount. This is way too many steps!!
I have two solutions in today's video:
1) Subtotal by ID and use Select Visible Cells to pull the Name down into the subtotal row
2) Use Excel Power Query to group by ID and Name.
In a "subtotal by" task where I use three fields (ID / Name / Amount) to get the total amount, Excel makes me choose between ID or Name for "at each change in" but I'd like to retain both so I currently perform data gymnastics to concatenate the ID and Name fields.....and then after using the function, I perform more gymnastics after selecting level 2, "go to special" / visible cells only on the subtotals, paste to another sheet, and then split the ID and name back out with the subtotaled amount. This is way too many steps!!
I have two solutions in today's video:
1) Subtotal by ID and use Select Visible Cells to pull the Name down into the subtotal row
2) Use Excel Power Query to group by ID and Name.
Transcript of the video:
Learn Excel from MrExcel Podcast, episode 2381. How to subtotal by both ID and name.
Great question from Kara, in my Akron webinar this week.
"I need a subtotal by both ID and name". Currently, Kara is concatenating them in a temporary column, adding the subtotals.
But then she needs to break it back into two columns, it's just a lot of steps.
Is there something quicker? Let's take a look.
Oh, great question today from Kara who is subtotaling data and needs to subtotal by both ID and Customer. Right now she's concatenating those together.
I don't know if my method will be much faster.
So we're going to do data subtotal at each change in ID, choose the four numeric fields out there, click OK.
All right, now check this out.
The problem here is that 12027 total appears, but we don't have the word ABC stores in column F. So I'm going to go to the number two view.
I'm going to choose all of those cells in column F, and then alt semicolon to select visible cells.
Now, currently I have to look over here on the left-hand side and see that I'm currently in F6.
I want to grab the customer name from just above me, so I'm going to very carefully here, type "=F5", and that formula's going to be different every day.
From this point, press Ctrl-enter, right and check out what just happened.
I brought the customer name down into this cell.
Here, let's look at that one, we'll expand back to the number three view.
I brought the word AT&T down into that current cell and so on. From there, we've got it, right.
We're just going to select all this data, alt-semicolon, Ctrl-C, Ctrl-N for new, Ctrl-V to paste, and we're good to go. I don't know, Kara, is that faster?
Now look, everyone, Kara's been in my seminars in Akron, Ohio many times before and Kara doesn't understand that the folks at this YouTube channel are all screaming at us right now because we're not using Power Query.
Particularly if the ID and customer could be different, so the same ID could have different customer names and you would need two lines.
They're all going to say that we should be using Power Querying.
So to do that, we're going to do Ctrl-T, and I understand how much we hate to do Ctrl-T because now we can no longer do subtotals.
Then on the Data tab we're going to say "From Table/Range".
We're going to click on ID, Ctrl click on Customer "Transform", "Group By".
So ID and customer at a new column called Quantity and the operation is going to be the sum of Quantity at another aggregation, Total Revenue, Sum, why does auto complete not work there? Of Revenue, Total Cost of goods sold.
Now I know this is like 400 more steps than the subtotal and Kara's already just shaking her head.
The beautiful thing though is once we do this once, it will be able to do this again and again and again, provided we can just paste the new data back over that table.
So while this certainly seems like it takes a lot longer, it will in the long run be a faster way to go. I got a great note back from Kara.
She says she's going to use both of these.
If it's something she's going to repeat a lot, she'll use Power Query, otherwise, she'll just do the quick and dirty method one.
Thanks to Kara for sending that question in and thanks to you for stopping by.
We'll see you next time for another Netcast from MrExcel.
Great question from Kara, in my Akron webinar this week.
"I need a subtotal by both ID and name". Currently, Kara is concatenating them in a temporary column, adding the subtotals.
But then she needs to break it back into two columns, it's just a lot of steps.
Is there something quicker? Let's take a look.
Oh, great question today from Kara who is subtotaling data and needs to subtotal by both ID and Customer. Right now she's concatenating those together.
I don't know if my method will be much faster.
So we're going to do data subtotal at each change in ID, choose the four numeric fields out there, click OK.
All right, now check this out.
The problem here is that 12027 total appears, but we don't have the word ABC stores in column F. So I'm going to go to the number two view.
I'm going to choose all of those cells in column F, and then alt semicolon to select visible cells.
Now, currently I have to look over here on the left-hand side and see that I'm currently in F6.
I want to grab the customer name from just above me, so I'm going to very carefully here, type "=F5", and that formula's going to be different every day.
From this point, press Ctrl-enter, right and check out what just happened.
I brought the customer name down into this cell.
Here, let's look at that one, we'll expand back to the number three view.
I brought the word AT&T down into that current cell and so on. From there, we've got it, right.
We're just going to select all this data, alt-semicolon, Ctrl-C, Ctrl-N for new, Ctrl-V to paste, and we're good to go. I don't know, Kara, is that faster?
Now look, everyone, Kara's been in my seminars in Akron, Ohio many times before and Kara doesn't understand that the folks at this YouTube channel are all screaming at us right now because we're not using Power Query.
Particularly if the ID and customer could be different, so the same ID could have different customer names and you would need two lines.
They're all going to say that we should be using Power Querying.
So to do that, we're going to do Ctrl-T, and I understand how much we hate to do Ctrl-T because now we can no longer do subtotals.
Then on the Data tab we're going to say "From Table/Range".
We're going to click on ID, Ctrl click on Customer "Transform", "Group By".
So ID and customer at a new column called Quantity and the operation is going to be the sum of Quantity at another aggregation, Total Revenue, Sum, why does auto complete not work there? Of Revenue, Total Cost of goods sold.
Now I know this is like 400 more steps than the subtotal and Kara's already just shaking her head.
The beautiful thing though is once we do this once, it will be able to do this again and again and again, provided we can just paste the new data back over that table.
So while this certainly seems like it takes a lot longer, it will in the long run be a faster way to go. I got a great note back from Kara.
She says she's going to use both of these.
If it's something she's going to repeat a lot, she'll use Power Query, otherwise, she'll just do the quick and dirty method one.
Thanks to Kara for sending that question in and thanks to you for stopping by.
We'll see you next time for another Netcast from MrExcel.
