Today's Learn Excel Podcast features a First for MrExcel! A Three-Level Side-to-Side Sort in Excel..."This is the first time that I've ever tried this, and it works!"
...This is the podcast companion to the book, Learn Excel 97-2007 from MrExcel.
...This is the podcast companion to the book, Learn Excel 97-2007 from MrExcel.
Transcript of the video:
MrExcel Podcast is sponsored by Easy-XL.
Learn Excel from MrExcel podcast episode 1345: Sorts Side to Side.
Hey welcome back to the MrExcel netcast and I’m Bill Jelen.
I'm starting out here and I have no idea if this is actually going to work.
Over the last couple of podcasts, I've matched this data up from the US to the Denmark sheet.
Have all the USA columns here and then the Denmark columns and you know--I would like to have USA and Denmark right next to each other.
Kind of wondered if I could use the Sort Columns to do this.
So I'm going to get rid of this extra column over here and I'm going to insert a new row up here and for these two cells I’m going to put the letter A to make sure they sort together and all of these cells put the letter B and Ctrl+Enter.
All right, now I know that I'm wanting to have to select that entire range and we'll go to Data, Sort, under options I'm going to say that I want to sort left to right, click OK.
Now I've never done a three level sort.
So what I’m going to do is I'm going to say sort by row 2, sort on values A to Z.
Add a level, sort on Row 3 nope row 4; that's the Year, sort on value sort A to Z and then add another level sort on Row 3, that's the country I want.
USA first and then Denmark.
So this one I’m going to say Z to A and click OK.
Hold my breath.
Sort numbers and numbers stored is text separately, that's funny there must be some of these years so anything that looks like a number sort as a number.
Click OKAY.
All right, so what do we get?
The A column stayed exactly where they were, that's good that's what I wanted then we have 2001 USA, 2001 Denmark, 2002 USA, 2002 Denmark and right on across all the way.
There we go.
Beautiful.
We can get rid of that extra row now, row 2, delete and we've put the data into a more usable format using a three-level sort columns.
Pretty slick if I say so myself.
I'm glad it worked never done that.
I always add an extra column to show which order I wanted the columns to go into but if there’s data there that's going to work.
All right, hey I want to thank you for stopping by we will see you next time another netcast from MrExcel.
Learn Excel from MrExcel podcast episode 1345: Sorts Side to Side.
Hey welcome back to the MrExcel netcast and I’m Bill Jelen.
I'm starting out here and I have no idea if this is actually going to work.
Over the last couple of podcasts, I've matched this data up from the US to the Denmark sheet.
Have all the USA columns here and then the Denmark columns and you know--I would like to have USA and Denmark right next to each other.
Kind of wondered if I could use the Sort Columns to do this.
So I'm going to get rid of this extra column over here and I'm going to insert a new row up here and for these two cells I’m going to put the letter A to make sure they sort together and all of these cells put the letter B and Ctrl+Enter.
All right, now I know that I'm wanting to have to select that entire range and we'll go to Data, Sort, under options I'm going to say that I want to sort left to right, click OK.
Now I've never done a three level sort.
So what I’m going to do is I'm going to say sort by row 2, sort on values A to Z.
Add a level, sort on Row 3 nope row 4; that's the Year, sort on value sort A to Z and then add another level sort on Row 3, that's the country I want.
USA first and then Denmark.
So this one I’m going to say Z to A and click OK.
Hold my breath.
Sort numbers and numbers stored is text separately, that's funny there must be some of these years so anything that looks like a number sort as a number.
Click OKAY.
All right, so what do we get?
The A column stayed exactly where they were, that's good that's what I wanted then we have 2001 USA, 2001 Denmark, 2002 USA, 2002 Denmark and right on across all the way.
There we go.
Beautiful.
We can get rid of that extra row now, row 2, delete and we've put the data into a more usable format using a three-level sort columns.
Pretty slick if I say so myself.
I'm glad it worked never done that.
I always add an extra column to show which order I wanted the columns to go into but if there’s data there that's going to work.
All right, hey I want to thank you for stopping by we will see you next time another netcast from MrExcel.
