In today's podcast, we start with a data set where column A contains both Region and Model information. In Episode 702, I'll use formulas to split that data into two columns.
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:
Welcome back to the MrExcel netcast. I'm Bill Jelen.
Now today I have a data set and eventually I want to do some data analysis with this data set like a pivot table or subtitles but currently it's in completely the wrong format.
There's so many problems with this data set.
We have blank rows and blank columns.
Here in column A, they didn't bother to fill in the word east for each of the detail rows and also in column A we have both region and model information.
Two different fields occupying column A.
They were creating a nice-looking outline view, which is great if you're printing this spreadsheet but horrible if you want to create a pivot table from it.
So over the next couple of episodes, we'll take a look at how we're going to clean out this data.
First thing I need to do is attack column A.
I want to make column A be two columns, One for models and one for regions.
So I'm going to go to there, to column A and we'll use "Insert" "Insert sheet columns" and I'll have a new heading called model and a heading called region.
Now I need to take a look at the data and figure out some clue.
Some clue to say that this line is a model and not a region.
Well in this case, It's pretty easy because we have the word model, and I can do =LEFT(B2) for five characters, and we see that we get them [ inaudiable ] model, so I'll say =IF if the left is equal to model.
Alright, then what do I know at that point, I know that this is basically the new heading for a brand new section, and I want to bring that data over from B2.
Otherwise, it's one of the other lines and most of the times it's going to be one of the other lines.
I just want to take the value from immediately above me in A1, so we'll copy that, let me make it a little bit wider.
Copy down to all of our rows and do a little test to make sure that as we go from 2500 to 3500 and it actually is working 3500 4500 and 6000B now we're in great shape.
Now later we're going to be sorting this data and ofcourse we want to freeze that model information now that we have it.
So I'm going to select all the data control+shift+ down arrow.
I'll use Ctrl+C to copy and then simply Paste "Paste Values" Have you excel 2003, look at the paste icon in the toolbar next to it is a drop down where you can get to paste values.
Now it's obvious in 2003 that you can do that. All right, so there we go.
We fixed the problem with column A , now having model. Column B later is going to become region.
Tomorrow we'll take a look at how to fill in all those blank cells in column B.
Well, thanks for stopping by. We'll see you next time for another netcast from MrExcel.
Now today I have a data set and eventually I want to do some data analysis with this data set like a pivot table or subtitles but currently it's in completely the wrong format.
There's so many problems with this data set.
We have blank rows and blank columns.
Here in column A, they didn't bother to fill in the word east for each of the detail rows and also in column A we have both region and model information.
Two different fields occupying column A.
They were creating a nice-looking outline view, which is great if you're printing this spreadsheet but horrible if you want to create a pivot table from it.
So over the next couple of episodes, we'll take a look at how we're going to clean out this data.
First thing I need to do is attack column A.
I want to make column A be two columns, One for models and one for regions.
So I'm going to go to there, to column A and we'll use "Insert" "Insert sheet columns" and I'll have a new heading called model and a heading called region.
Now I need to take a look at the data and figure out some clue.
Some clue to say that this line is a model and not a region.
Well in this case, It's pretty easy because we have the word model, and I can do =LEFT(B2) for five characters, and we see that we get them [ inaudiable ] model, so I'll say =IF if the left is equal to model.
Alright, then what do I know at that point, I know that this is basically the new heading for a brand new section, and I want to bring that data over from B2.
Otherwise, it's one of the other lines and most of the times it's going to be one of the other lines.
I just want to take the value from immediately above me in A1, so we'll copy that, let me make it a little bit wider.
Copy down to all of our rows and do a little test to make sure that as we go from 2500 to 3500 and it actually is working 3500 4500 and 6000B now we're in great shape.
Now later we're going to be sorting this data and ofcourse we want to freeze that model information now that we have it.
So I'm going to select all the data control+shift+ down arrow.
I'll use Ctrl+C to copy and then simply Paste "Paste Values" Have you excel 2003, look at the paste icon in the toolbar next to it is a drop down where you can get to paste values.
Now it's obvious in 2003 that you can do that. All right, so there we go.
We fixed the problem with column A , now having model. Column B later is going to become region.
Tomorrow we'll take a look at how to fill in all those blank cells in column B.
Well, thanks for stopping by. We'll see you next time for another netcast from MrExcel.
