A dueling podcast: how to unwind a rectangular selection to a single column. Mike & Bill weigh in with differing methods in Episode 1010.
This video is the 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 video is the 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! I'm Bill Jelen, from MrExcel.com.
I've got a cool excel tip for you, today.
Hey, this is Mike Gel Girvin from Excel Is Fun on YouTube and I have a different way to do that.
Here we are it's another dueling Excel podcast.
Great question send in Gary.
This time Gary is interested in using the analysis Tool pack, the descriptive statistics feature.
That's not what the podcast is about.
He says, "Hey! Look that feature requires my set of numbers to be any other one row or one column having it in a rectangular range does not work.
So, how can I take this data, copy the data and paste special, unwind it." Basically, is what he wants to do either into a single column or a single row.
Alright! So I'm gonna throw this one over to Mike first and see what solution Mike has.
We'll come back. I'll show you a macro solution. Mike!
Mike: Hey! Thanks MrExcel.
Hey! Is this a great question or what?
You know I teach and I get textbook statistics.
Textbooks even worse Excel textbooks and the students are given blocks, a tattle like this.
You can't do a lot of the great data, analysis features in Excel with data like this.
You need in a column or a row.
Now, I want to show you a couple things first before I show you my trick.
Under data, here's the data analysis and this has some great statistical tools in it.
You got to go to the office button and down the options here the keyboard [ Shortcut ] for that is [ alt + F + I ].
Great keyboard circuit because there's lots of great tricks in the Excel options.
So, you come down to add-ins and down here.
You select that add-in and Click [ go ].
There it is analysis Tool pack.
and then that will show that right there in 2007.
In earlier versions, it will not only give you the data analysis feature but it will also add some extra functions for you.
I'm going to Click [ ok ] Now I also got to show you on the home Ribbon the Clipboard because the trick I'm going to show you, it involves the clipboard.
Now, in earlier versions you could [ control + CC ] and open up the clipboard.
It would copy it and open up the clipboard.
I better clear all here.
They're all.
Now, down here clipboard in, under options.
There it is.
So, you can use the keyboard shortcut from earlier versions but you gotta check this [ control CC ], to show the office clipboard.
Now, I'm going to close this and start the trick here.
I have my cursor right there.
I'm going to [ control + shift +down arrow ], and then [ cTRl + CC ], that opens it up.
The active cell is right there.
I'm going to [ right arrow ], and now I want to [ control + shift + down arrow ] , [ control + C ].
[ Right arrow ], [ control shift down arrow ] [ control C ]. [ Right arrow ] [ control C ], [ down arrow ] and [ control C ] No way because I have the clipboard open and collected it there and here's the easy way to do this.
Except for, if you know VBA.
Paste all whoop! Just like that.
It pastes it all in a column.
Now, I'm going to come up to the top, [ control + up arrow ] and type sales or whatever the label is and now let's go ahead and try that data analysis right there boo!
And there's great data analysis statistics.
I'm just going to do the descriptive statistics, click [ ok ].
Now, I've got the range right.
I have checked this for labels in first row.
I've checked all these because I want summary statistics largest, smallest.
And then I simply click [ ok ] and just like that.
It will insert a sheet and I'm going to put 'S' for stats.
And there you have it to a nice quick way and because we have our data in a column.
Now, I'm going to throw us back to MrExcel, and we'll get to learn some VBA.
MrExcel: Nice Mike! That clipboard trick.
Really, cool. Yeah, I'm a VBA guy.
I'm just going to switch over to VBA here, and solve this with a few lines of code.
Insert a module call it the 'sub' unwind selection.
I'm gonna set up a couple of variables here.
Dim out cell as range.
Counter is equal to 0.
I'm going to define the out cell as being two columns to the right of the range.
So, set out cell.
We have to use set because it is a object variable, equal to selection.
Dot cells 1 comma 1. (.cells1,1) I'll just go to the top right hand corner of the selection dot offset 0 rows down and then selection dot columns count plus 2 that's going to get me outside of the cell and then from here it's a three simple lines of code for each cell in selection.
Outsell dot offset counter comma zero dot value is equal to cell dot value counter equals counter plus 1 and then next cell.
Alright! We're going to test it, first going to go into macros and find unwind selection, click on options and assign it to a shortcut key [ ctrl shift U ].
So, I press [ shift U ] there and we'll select these cells and [ Ctrl shift U ] and there we are, our entire set of numbers.
Let's just do a quick test to make sure, we got them all.
Here the quick sub says 43117 and here the quick sub 43117.
Set that up in your personal macro workbook and then you'll have [ control shift U ], available to all the time.
Of course, be careful that the data to the right of your selection is not filled.
This is just going to blindly, over right.
Hey! I want to thank you for stopping by, behalf of Mike and myself.
We appreciate you watching these dueling Excel podcast.
We'll catch you next time.
I've got a cool excel tip for you, today.
Hey, this is Mike Gel Girvin from Excel Is Fun on YouTube and I have a different way to do that.
Here we are it's another dueling Excel podcast.
Great question send in Gary.
This time Gary is interested in using the analysis Tool pack, the descriptive statistics feature.
That's not what the podcast is about.
He says, "Hey! Look that feature requires my set of numbers to be any other one row or one column having it in a rectangular range does not work.
So, how can I take this data, copy the data and paste special, unwind it." Basically, is what he wants to do either into a single column or a single row.
Alright! So I'm gonna throw this one over to Mike first and see what solution Mike has.
We'll come back. I'll show you a macro solution. Mike!
Mike: Hey! Thanks MrExcel.
Hey! Is this a great question or what?
You know I teach and I get textbook statistics.
Textbooks even worse Excel textbooks and the students are given blocks, a tattle like this.
You can't do a lot of the great data, analysis features in Excel with data like this.
You need in a column or a row.
Now, I want to show you a couple things first before I show you my trick.
Under data, here's the data analysis and this has some great statistical tools in it.
You got to go to the office button and down the options here the keyboard [ Shortcut ] for that is [ alt + F + I ].
Great keyboard circuit because there's lots of great tricks in the Excel options.
So, you come down to add-ins and down here.
You select that add-in and Click [ go ].
There it is analysis Tool pack.
and then that will show that right there in 2007.
In earlier versions, it will not only give you the data analysis feature but it will also add some extra functions for you.
I'm going to Click [ ok ] Now I also got to show you on the home Ribbon the Clipboard because the trick I'm going to show you, it involves the clipboard.
Now, in earlier versions you could [ control + CC ] and open up the clipboard.
It would copy it and open up the clipboard.
I better clear all here.
They're all.
Now, down here clipboard in, under options.
There it is.
So, you can use the keyboard shortcut from earlier versions but you gotta check this [ control CC ], to show the office clipboard.
Now, I'm going to close this and start the trick here.
I have my cursor right there.
I'm going to [ control + shift +down arrow ], and then [ cTRl + CC ], that opens it up.
The active cell is right there.
I'm going to [ right arrow ], and now I want to [ control + shift + down arrow ] , [ control + C ].
[ Right arrow ], [ control shift down arrow ] [ control C ]. [ Right arrow ] [ control C ], [ down arrow ] and [ control C ] No way because I have the clipboard open and collected it there and here's the easy way to do this.
Except for, if you know VBA.
Paste all whoop! Just like that.
It pastes it all in a column.
Now, I'm going to come up to the top, [ control + up arrow ] and type sales or whatever the label is and now let's go ahead and try that data analysis right there boo!
And there's great data analysis statistics.
I'm just going to do the descriptive statistics, click [ ok ].
Now, I've got the range right.
I have checked this for labels in first row.
I've checked all these because I want summary statistics largest, smallest.
And then I simply click [ ok ] and just like that.
It will insert a sheet and I'm going to put 'S' for stats.
And there you have it to a nice quick way and because we have our data in a column.
Now, I'm going to throw us back to MrExcel, and we'll get to learn some VBA.
MrExcel: Nice Mike! That clipboard trick.
Really, cool. Yeah, I'm a VBA guy.
I'm just going to switch over to VBA here, and solve this with a few lines of code.
Insert a module call it the 'sub' unwind selection.
I'm gonna set up a couple of variables here.
Dim out cell as range.
Counter is equal to 0.
I'm going to define the out cell as being two columns to the right of the range.
So, set out cell.
We have to use set because it is a object variable, equal to selection.
Dot cells 1 comma 1. (.cells1,1) I'll just go to the top right hand corner of the selection dot offset 0 rows down and then selection dot columns count plus 2 that's going to get me outside of the cell and then from here it's a three simple lines of code for each cell in selection.
Outsell dot offset counter comma zero dot value is equal to cell dot value counter equals counter plus 1 and then next cell.
Alright! We're going to test it, first going to go into macros and find unwind selection, click on options and assign it to a shortcut key [ ctrl shift U ].
So, I press [ shift U ] there and we'll select these cells and [ Ctrl shift U ] and there we are, our entire set of numbers.
Let's just do a quick test to make sure, we got them all.
Here the quick sub says 43117 and here the quick sub 43117.
Set that up in your personal macro workbook and then you'll have [ control shift U ], available to all the time.
Of course, be careful that the data to the right of your selection is not filled.
This is just going to blindly, over right.
Hey! I want to thank you for stopping by, behalf of Mike and myself.
We appreciate you watching these dueling Excel podcast.
We'll catch you next time.
