It is frustrating when section headers contain data that applies to all records in that section. You really want to get that header data down onto every row in the section. Episode 826 shows you one method for doing this.
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.
Well, we have an interesting problem today.
Someone sent in this spreadsheet.
It's a beautiful-looking spreadsheet with all these great little headers here.
The header gives me the department name and the account number, and then I get the headings FTE, NAME, and TOTAL, and then all the information for that person, and a great little TOTAL at the end, and then the next department, and, all the way down through, looks gorgeous.
Impossible to do anything with this.
If you want to do sorting or pivot tables, we've lost the ability to do all that, and so we're going to take a look at how to solve this, how to make this back into a good data set that we can actually sort and pivot and total and do all those great things with.
I’m going to insert a couple of new columns over here, one called POSITION and one called ACCOUNT NUMBER, and I'm going to attack the ACCOUNT NUMBER first.
Basically, when I look in what is now column D, I see that all of our account numbers have 4 digits and then a -, and so I'm interested in that -. I say =IF, if the MID of column D starting in position 5 for a length of 1 is = to a -, then I want that value from column D, otherwise I want the value from immediately above me, and watch what happens when I copy this down.
Basically, I'm going to get the same account number all the way down, we'll make it a little bit wider, until we get to the next - over here in column D, and then it brings that new account number over.
[ =IF(MID(D2,5,1)=“-”,D2,B1) ] Now, the same logic is going to apply in column A to grab the position.
I'll copy this formula over and I'll say if the MID of D2 is -, then I want C2, otherwise I want A1.
Alright.
So, again, that's going to do the same thing.
It's going to copy the position from our headings up there over to A and B. [ =IF(MID(D2,5,1)=“-”,C2,A1) ] So, let's copy these formulas all the way down to the end of our data set.
Of course, I can't double click the fill handle here because there's spaces throughout the data.
CONTROL+V to paste.
Now, before I can delete anything, I want to change all those formulas to values.
So, copy, and then edit, PASTE SPECIAL, VALUES.
Alright.
Simple enough.
Now, we need to get rid of all the extra stuff.
So, here we have a column that’s FTE, a column that’s NAME, and a column that’s TOTAL, and so what I want to do is I want to sort this entire data set by column D.
I'll select the whole data set with CONTROL*, and then we'll go into the SORT dialog box to make sure that it understands that our data has headers.
I'm going to SORT BY column D, the NAME column, just in simple AtoZ order, and what that's going to do for me is it's going to put all of the account numbers together.
So, I'll take all of those account numbers and I'll delete those.
It's also going to put all the word NAME together in a nice block, I'll delete those, and, at the bottom, we're going to see the TOTALs, we’ll get rid of those, and, finally, anything without a name, those were all the blank rows that were between the data and our totals, I can just simply delete all of those.
So, now, we're left with only the things that had a true name there in column D, and what we've achieved is a nice data set now that allows us to sort by POSITION, by ACCOUNT, or by anything else, and we can create pivot tables.
Now, you'll see I have some formatting issues there.
I'd like to clear all that formatting.
Now, in tomorrow's netcast, we'll take a look at how to figure out totals for each account.
The reason that they had the data set up with this is there was another sheet that was linking in to grab all those totals.
Well, we can change that sheet to use a formula instead of simple links.
Thanks for stopping by.
We’ll see you next time for another netcast from MrExcel.
Well, we have an interesting problem today.
Someone sent in this spreadsheet.
It's a beautiful-looking spreadsheet with all these great little headers here.
The header gives me the department name and the account number, and then I get the headings FTE, NAME, and TOTAL, and then all the information for that person, and a great little TOTAL at the end, and then the next department, and, all the way down through, looks gorgeous.
Impossible to do anything with this.
If you want to do sorting or pivot tables, we've lost the ability to do all that, and so we're going to take a look at how to solve this, how to make this back into a good data set that we can actually sort and pivot and total and do all those great things with.
I’m going to insert a couple of new columns over here, one called POSITION and one called ACCOUNT NUMBER, and I'm going to attack the ACCOUNT NUMBER first.
Basically, when I look in what is now column D, I see that all of our account numbers have 4 digits and then a -, and so I'm interested in that -. I say =IF, if the MID of column D starting in position 5 for a length of 1 is = to a -, then I want that value from column D, otherwise I want the value from immediately above me, and watch what happens when I copy this down.
Basically, I'm going to get the same account number all the way down, we'll make it a little bit wider, until we get to the next - over here in column D, and then it brings that new account number over.
[ =IF(MID(D2,5,1)=“-”,D2,B1) ] Now, the same logic is going to apply in column A to grab the position.
I'll copy this formula over and I'll say if the MID of D2 is -, then I want C2, otherwise I want A1.
Alright.
So, again, that's going to do the same thing.
It's going to copy the position from our headings up there over to A and B. [ =IF(MID(D2,5,1)=“-”,C2,A1) ] So, let's copy these formulas all the way down to the end of our data set.
Of course, I can't double click the fill handle here because there's spaces throughout the data.
CONTROL+V to paste.
Now, before I can delete anything, I want to change all those formulas to values.
So, copy, and then edit, PASTE SPECIAL, VALUES.
Alright.
Simple enough.
Now, we need to get rid of all the extra stuff.
So, here we have a column that’s FTE, a column that’s NAME, and a column that’s TOTAL, and so what I want to do is I want to sort this entire data set by column D.
I'll select the whole data set with CONTROL*, and then we'll go into the SORT dialog box to make sure that it understands that our data has headers.
I'm going to SORT BY column D, the NAME column, just in simple AtoZ order, and what that's going to do for me is it's going to put all of the account numbers together.
So, I'll take all of those account numbers and I'll delete those.
It's also going to put all the word NAME together in a nice block, I'll delete those, and, at the bottom, we're going to see the TOTALs, we’ll get rid of those, and, finally, anything without a name, those were all the blank rows that were between the data and our totals, I can just simply delete all of those.
So, now, we're left with only the things that had a true name there in column D, and what we've achieved is a nice data set now that allows us to sort by POSITION, by ACCOUNT, or by anything else, and we can create pivot tables.
Now, you'll see I have some formatting issues there.
I'd like to clear all that formatting.
Now, in tomorrow's netcast, we'll take a look at how to figure out totals for each account.
The reason that they had the data set up with this is there was another sheet that was linking in to grab all those totals.
Well, we can change that sheet to use a formula instead of simple links.
Thanks for stopping by.
We’ll see you next time for another netcast from MrExcel.
