Sam from Vienna sends in today's question. In a database of events, how can he add new blank rows to represent every missing day? Episode 1103 shows you one method.
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.
Basically, you start out with massive amount of data.
So, how we're gonna analyze this.
Well, let's fire up a Pivot Table and see if you can solve this problem.
Welcome back to the MrExcel netcast.
I'm Bill Jelen.
Today's question from Sam in Vienna.
Sam has a schedule of all his classes and you see that there are some days, where Sam does not have classes, there's no class on August 26.
He said everywhere, where there's not a class, on that day he'd like to insert a blank row, so that way he could, you know, make notes of, you know, maybe what he's gonna do that day or something like that and so, initially I thought, okay, we're gonna build a little table, out on the right-hand side with all the Dates, do vlookups, to bring this data over and then, switch the N/A's to blanks or something like that, but the thing that kind of screwed that idea up, as you see that here, on the 27th of August, there are two classes 1 at 10:00 a.m. and 1 at 2:30, so that's not gonna work at all.
So now here's my 2nd approach to this.
I'm gonna come over here and put in the first day's date 8/24/2009 and I'm gonna figure out what the last day is?
so, I'll scroll down, January 14th.
I just grabbed the fill handle, so, we can build all these dates down to January 14th.
This is a list of all the dates that could possibly happen and then I'm gonna use the Match command.
I'm gonna come over here and say got a column called There?
equal match, go look for this date, within this range of dates over here.
Ctrl+shift down arrow, press F4 to lock that down, comma zero, saying we want an exact match and well, that's giving me a date, that's kind of funny. I really don't care about the results.
what I really care about are the N/A's, so, this is a 1, this is a 2, that's telling what row number it's on?
Really don't care there, I really just care about the N/A's.
Let's convert those to values and now we're going to sort descending.
Sort descending that'll bring all of the N/A's to the top, there we have it, those are all the dates that have no match over on the left-hand side.
So I'll take those dates, come down here and just paste it below my data.
ESV, I wanna take this date format, very clever, Tape format, EST for formats.
Now, we get our dates in there and we'll delete these extra columns, those temporary columns over there.
Now, what I think we can do, is come back here and sort by the start date, click AZ, let's see what happens, there it is, there are now blank rows for all of the cells, much easier than going through an individually inserting the rows, just come out to the right hand side, figure out what Dates are missing and then allow the power of the sort, to bring those right up into the proper spots.
Looks like, so here on December 11th, we have two rows, that's good because there were two classes but then on all of the other days, where there were no dates, works out pretty well.
Hey thanks to Sam for sending in, that question.
Sam says: "he watches the podcast every day" all the way from Vienna. Very cool.
You know another question and this is...
the thing that initially caught my eye is, I open this up on the US version of Excel but we're still getting a foreign date version there.
I wanna go into Ctrl+1, let's just see what...
Wow! Can't explain that, how we're getting another language, there for the day of the week.
Let's check out, Custom.
It must be that country code there, C07 ddddd.
Interesting, never knew that you could do that, not sure why you'd want to but there you have it.
Hey, thanks for stopping by. I'll see you next time for another netcast from MrExcel.
Basically, you start out with massive amount of data.
So, how we're gonna analyze this.
Well, let's fire up a Pivot Table and see if you can solve this problem.
Welcome back to the MrExcel netcast.
I'm Bill Jelen.
Today's question from Sam in Vienna.
Sam has a schedule of all his classes and you see that there are some days, where Sam does not have classes, there's no class on August 26.
He said everywhere, where there's not a class, on that day he'd like to insert a blank row, so that way he could, you know, make notes of, you know, maybe what he's gonna do that day or something like that and so, initially I thought, okay, we're gonna build a little table, out on the right-hand side with all the Dates, do vlookups, to bring this data over and then, switch the N/A's to blanks or something like that, but the thing that kind of screwed that idea up, as you see that here, on the 27th of August, there are two classes 1 at 10:00 a.m. and 1 at 2:30, so that's not gonna work at all.
So now here's my 2nd approach to this.
I'm gonna come over here and put in the first day's date 8/24/2009 and I'm gonna figure out what the last day is?
so, I'll scroll down, January 14th.
I just grabbed the fill handle, so, we can build all these dates down to January 14th.
This is a list of all the dates that could possibly happen and then I'm gonna use the Match command.
I'm gonna come over here and say got a column called There?
equal match, go look for this date, within this range of dates over here.
Ctrl+shift down arrow, press F4 to lock that down, comma zero, saying we want an exact match and well, that's giving me a date, that's kind of funny. I really don't care about the results.
what I really care about are the N/A's, so, this is a 1, this is a 2, that's telling what row number it's on?
Really don't care there, I really just care about the N/A's.
Let's convert those to values and now we're going to sort descending.
Sort descending that'll bring all of the N/A's to the top, there we have it, those are all the dates that have no match over on the left-hand side.
So I'll take those dates, come down here and just paste it below my data.
ESV, I wanna take this date format, very clever, Tape format, EST for formats.
Now, we get our dates in there and we'll delete these extra columns, those temporary columns over there.
Now, what I think we can do, is come back here and sort by the start date, click AZ, let's see what happens, there it is, there are now blank rows for all of the cells, much easier than going through an individually inserting the rows, just come out to the right hand side, figure out what Dates are missing and then allow the power of the sort, to bring those right up into the proper spots.
Looks like, so here on December 11th, we have two rows, that's good because there were two classes but then on all of the other days, where there were no dates, works out pretty well.
Hey thanks to Sam for sending in, that question.
Sam says: "he watches the podcast every day" all the way from Vienna. Very cool.
You know another question and this is...
the thing that initially caught my eye is, I open this up on the US version of Excel but we're still getting a foreign date version there.
I wanna go into Ctrl+1, let's just see what...
Wow! Can't explain that, how we're getting another language, there for the day of the week.
Let's check out, Custom.
It must be that country code there, C07 ddddd.
Interesting, never knew that you could do that, not sure why you'd want to but there you have it.
Hey, thanks for stopping by. I'll see you next time for another netcast from MrExcel.
