Bill: Hey I’m Bill Jelen from Mr. Excel.com and I have a cool Excel tip for you today. Hey this is Mike Gel Girvin in Excel is Fun in YouTube and I have a different way to do that.
All right, welcome back, it’s another Excel podcast, my favorite day of the week. I’m Bill Jelen from Mr. Excel I’m will be joined by Mike Girvin from Excel is fun.
Mike sent in this one from one of his folks today. They want to find the earliest encounter between a particular costumer and a particular sales rep. Now, I’m just going to go old school on this. Let’s just come in here, go to data and turn on the—actually even better than that. Let’s choose each rate and come up here and choose filter by selection and then filter by selection and sort the data A to Z—there it is—earliest is January 19 2008. What’s faster than that? Mike, lets see what you have.
Mike: Thanks Mr. Excel. Old school, yes that is the way to go so fast. Just a few clicks and not only that, you get the record. Hey, but if you were just interested in the date, you could just do a formula like this.
Now we’re interested in the earliest dates, so I’m going to use the MIN function—equals MIN, but we have two conditions so I’m going to use the IF function. IF and we’ll do costumer first, Ctrl + Shift + Down Arrow, and I’m going to use my up arrow key, Ctrl + Shift + Down Arrow and then hit the equal because we have to say, hey what in that range is equal to and then I’ll use my up arrow key and I’ll get my costumer and then comma, there is two conditions so I’ll use the second IF, I’ll come over and get the sales rep range Ctrl + Shift + Down Arrow equal and then up arrow and get Chin.
Those are the two conditions when it’s true and true, then what do we need? We need a range for the MIN and that will be this date Ctrl + Shift + Down Arrow and I have no quick way to jump off so I’m going to hit the F4 key three times to jump the screen back up and get rid of all the dollar signs.
Now I’m going to close parenthesis once, close parenthesis, I’m watching the screen tips and I finally I see I got to the MIN, so now I’m going to hit close parenthesis and what if I just hit enter? Zero?—That’s not it. This isn’t array formula, IF right here is expecting a single true of false task, we gave it array so we have to use Ctrl + Shift + Enter and there we have it.
Now if we change this to—say PPS Chin, then there is the earliest date all right? So, there is my method, I’ll throw it back to Mr. Excel.
Bill: And that is beautiful. I like the formula but pressing F4 three times to get back up to the top, what a cool, cool trick. Hey, I used Auto Filter, I want to talk about using advanced Filters so I choose – First, when you need to rearrange this criteria range, Ctrl + X to cut, Ctrl V to paste. I also noticed when Mike set this up, he created a name range for that column criteria, it seemed it’ll come up again later.
All right so we select one cell over our dataset, choose advance, I’m going to filter the list in place and there it shows the right area throughout my criteria range, H2 to I3 and click OK, what that does is it shows us just the matching records and then I can click A to Z to find the earliest date. That’s one way to go. Let’s clear that.
Another one, boy I don’t even know if I remember to use this because this is an old, old function. One of the data base functions DMIN. Equal DMIN, lets see what it asks for, it ask for the data base, well that’s this over here. So Ctrl + Shift + Right + Down and a comma, next it wants the field, I don’t remember if it ask for a number one or date. Let’s put date and see if that works. And then finally, the criteria range, same criteria range that we used for the advanced filter, so that uses Mike’s main range or criteria, kind of funny that even though I cut and paste that the criteria still pointed at the right area, but I guess it is.
All right so let’s press enter, there is our answer right answer, wrong format. Let’s do Ctrl + 1 to format that as a date. Click OK and 1-19-2008 let’s choose another sales rep and 1-6-2008—very cool, the old DMIN function. So there you go, lot of different ways to do. Thank you for stopping by, I want to thank Mike for being a part of the Excel Podcast. See you next time for another podcast from Mr. Excel and Excel is fun.
Transcription by:
Scribe4you Transcription Services