You need to sort a detailed data set so that the records for the largest customer are at the top. Episode 755 will show you a quick way to accomplish this task.
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
Transcript of the video:
Hey. Welcome back to the MrExcel netcast. I’m Bill Jelen.
Now, here's a question that came up in one of my Excel seminars.
The person said, hey, we have a large data set and I need to sort by customer.
I don't need to sort ascending or descending.
I need to sort it so, that way, the largest customer’s at the top.
Well, there's an amazing trick that allows you to do this.
What we're going to do is, first, sort the customers ascending.
So, I click one cell in the CUSTOMER column and click the AtoZ button to sort in an ascending fashion, and then I'm going to add subtotals by customer.
DATA, SUBTOTALS, AT EACH CHANGE IN CUSTOMER, and I'm going to use the SUM function on the REVENUE column, click OK, and of course it adds the automatic subtotals in.
Nothing amazing there.
Now I'm going to press the number 2 button.
You see we have 3 new buttons up here in the left-hand side, the 1, 2, 3, these are the group and outline buttons, those get added when we add automatic subtotals.
I'll press the 2 button, so that way it shows us just one line per customer.
Now, amazingly, if we choose one cell in the REVENUE column and sort descending, Excel hasn't sorted just the subtotals but it had sorted all of the individual rows as well.
So, when I press the number 3 button, you’ll see that Walmart's at the top followed by General Motors.
Now what we can do is remove the subtotals.
DATA, SUBTOTALS, REMOVE ALL, and we've effectively brought the largest customer and all of their detail records to the top.
Well, there you have it.
Thanks for stopping by.
We’ll see you next time for another netcast from MrExcel.
Now, here's a question that came up in one of my Excel seminars.
The person said, hey, we have a large data set and I need to sort by customer.
I don't need to sort ascending or descending.
I need to sort it so, that way, the largest customer’s at the top.
Well, there's an amazing trick that allows you to do this.
What we're going to do is, first, sort the customers ascending.
So, I click one cell in the CUSTOMER column and click the AtoZ button to sort in an ascending fashion, and then I'm going to add subtotals by customer.
DATA, SUBTOTALS, AT EACH CHANGE IN CUSTOMER, and I'm going to use the SUM function on the REVENUE column, click OK, and of course it adds the automatic subtotals in.
Nothing amazing there.
Now I'm going to press the number 2 button.
You see we have 3 new buttons up here in the left-hand side, the 1, 2, 3, these are the group and outline buttons, those get added when we add automatic subtotals.
I'll press the 2 button, so that way it shows us just one line per customer.
Now, amazingly, if we choose one cell in the REVENUE column and sort descending, Excel hasn't sorted just the subtotals but it had sorted all of the individual rows as well.
So, when I press the number 3 button, you’ll see that Walmart's at the top followed by General Motors.
Now what we can do is remove the subtotals.
DATA, SUBTOTALS, REMOVE ALL, and we've effectively brought the largest customer and all of their detail records to the top.
Well, there you have it.
Thanks for stopping by.
We’ll see you next time for another netcast from MrExcel.
