Hey, welcome back to the Mr. Excel netcast, I’m Bill Jelen. Basically, we start out with massive amounts of data and say how are we going to analyze this? Well, let’s fire up the pivot table and see if we can solve this problem.
Welcome back to the Mr. Excel netcast, I’m Bill Jelen. Today’s question from Sam Vienna. Sam has a schedule of all his classes and you see that there are some days where Sam does not have classes. There is no class on August 26. Then he said everywhere where there is not a class on that day, you’d like to insert a blank row so that way he could make notes maybe of what he’s going to do that day or something like that and so initially I thought okay, we’re going to build a little table out on the right hand side with all the dates do V lookups to bring this data over and then switch the NAs to blanks or something like that but the thing that kind of screwed that idea up is that you see that here on the 27th of August there were two classes, one at ten a.m. and one at 2:30 so that’s not going to work at all. So now here is my second approach in this.
I’m going to come over here and put in the first dates, date 8/24/2009 and I’m going to figure out what the last days. I’m going to scroll down, January 14th. Let’s just grab the fill handle and so we can built all these dates down to January 14th. This is a list of all the dates that could possibly happen and then I’m going to use the match command. I’m going to come over here and say, go to column there, = MATCH, go look for this date within these range of dates over here, Ctrl-Shift-down arrow. Press F4 to lock that down. Comma, zero, segment one and exact match and well that’s give me a date, that’s kind of funny. I really don’t care about the results, what I really care about are the NAs so this is the one, this is a two that tells me what row number a time. I really don’t care there I really just care about the NAs.
Let’s convert those to values and now we are going to sort descending. Descending that will bring all of the NAs to the top, there we have it. Those are all the dates that have no match over the left hand side. So I’ll take those dates, come down here and just paste it below my data, ESV. I’m going to take this date format, very clever date format, EST for format and we get our dates in there and we’ll delete these extra columns, there’s temporary columns over there.
What I think we can do is come back here and sort by this start date, click AZ, let’s see what happens, there it is. There are now blank rows for all of the cells, much easier that 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. So here on December 11th we have two rows. That’s good because there were two classes but then in all of the other days where there were no dates, it works out pretty well.
Hey thanks to Sam for sending in that question. Sam says he watches the podcast everyday all the way from Vienna, very cool. Another question and this is the thing that initially caught my eyes. I opened this up on the US version of Excel but we’re still getting a foreign date version there.
When I got to control one, let’s just see what—I can’t explain that how we are getting another language there for the day of the week. Let’s check out custom. Well, it must be that country code there, that’s a CO7, interesting. I never knew that you could do that. I’m not sure why you want to but there you have it.
Hey, thanks for stopping by! I’ll see you next time on the next netcast from Mr. Excel.
Transcription by:
Scribe4you Transcription Services