In this Dueling Excel podcast, how to find the earliest date for records that match two criteria. Episode 1104 shows you how.
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, I'm Bill Jelen from MrExcel.com, and I've got a cool excel tip for you today.
Hey, this is Mike Gel Gervin at Excel Is Fun in YouTube, and I have a different way to do that.
All right, welcome back. It's another dueling excel podcast. My favorite day of the week.
I'm Bill Jelen from MrExcel, will be joined by Mike Gervin 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 customer 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 auto, actually, you know, hey, even better than that let's choose ETRADE, and come up here and choose filter by selection.
And then Chin, filter by selection, and then sort the data A to Z.
There it is. Earliest is january 19, 2008. What's faster than that?
Mike. Let's see what you have.
Thanks MrExcel. 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 date, so I'm going to use the MIN function.
= MIN but we have two conditions.
So, I'm going to use the IF function. IF.
And we'll do customer first.
Ctrl+Shift+down arrow and then 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 customer, and then comma. There's two conditions, so I'll use a 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'll be this date. Ctrl+Shift+down arrow.
And i have no quick way to jump up, so I'm going to hit the F4 key 3 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 finally I see, I got to the MIN.
So now, I'm going to hit close parenthesis and what if I just hit Enter?
0?
That's not it. This is an array formula.
IF right here, is expecting a single TRUE or FALSE test.
We gave it an array, so we have to use Ctrl+Shift and Enter and there we have it.
Now, if we change this to, say PPS Chin, then there's the earliest date.
All right.There's my method, I'll throw it back to MrExcel.
That is beautiful! I like the formula, but pressing F4 3 times to get back up to the top, what a cool cool trick.
Hey, I used Autofilter, want to talk about using advanced filter, so i choose, well, first we need to rearrange this criteria range.
Ctrl+X to cut, Ctrl+V to paste.
Also notice that when Mike set this up, he created a named range for that color criteria, there it is.
So, we'll be seeing that, come up again later.
All right, so we select 1 cell in our data set.
Choose Advanced.
I'm going to filter the list in place, and there it shows the right area A3 of my criteria range H2 to I3 and click OK. What that does is, it shows us just the matching records and then, I 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 how to use this, this is an old, old, old function.
One of the database functions DMIN =DMIN Let's see what it asked for. It asked for the database.
Okay well, that's this over here.
So, Ctrl+Shift right, down and a comma next it wants the field.
Well, I don't even remember if it asks for a number 1 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 as you said, that uses Mike's named range of criteria.
Kind of funny that even though I cut and paste that, the criteria is still pointing to the right area but, yeah, I guess it is.
All right,so let's press Enter.
There's 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 functions.
There you go, a lot of different ways to do it.
Want thank you for stopping by. Want to thank Mike for being a part of the dueling Excel podcast.
See you next time, for another netcast from MrExcel and Excel Is Fun.
Hey, this is Mike Gel Gervin at Excel Is Fun in YouTube, and I have a different way to do that.
All right, welcome back. It's another dueling excel podcast. My favorite day of the week.
I'm Bill Jelen from MrExcel, will be joined by Mike Gervin 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 customer 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 auto, actually, you know, hey, even better than that let's choose ETRADE, and come up here and choose filter by selection.
And then Chin, filter by selection, and then sort the data A to Z.
There it is. Earliest is january 19, 2008. What's faster than that?
Mike. Let's see what you have.
Thanks MrExcel. 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 date, so I'm going to use the MIN function.
= MIN but we have two conditions.
So, I'm going to use the IF function. IF.
And we'll do customer first.
Ctrl+Shift+down arrow and then 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 customer, and then comma. There's two conditions, so I'll use a 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'll be this date. Ctrl+Shift+down arrow.
And i have no quick way to jump up, so I'm going to hit the F4 key 3 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 finally I see, I got to the MIN.
So now, I'm going to hit close parenthesis and what if I just hit Enter?
0?
That's not it. This is an array formula.
IF right here, is expecting a single TRUE or FALSE test.
We gave it an array, so we have to use Ctrl+Shift and Enter and there we have it.
Now, if we change this to, say PPS Chin, then there's the earliest date.
All right.There's my method, I'll throw it back to MrExcel.
That is beautiful! I like the formula, but pressing F4 3 times to get back up to the top, what a cool cool trick.
Hey, I used Autofilter, want to talk about using advanced filter, so i choose, well, first we need to rearrange this criteria range.
Ctrl+X to cut, Ctrl+V to paste.
Also notice that when Mike set this up, he created a named range for that color criteria, there it is.
So, we'll be seeing that, come up again later.
All right, so we select 1 cell in our data set.
Choose Advanced.
I'm going to filter the list in place, and there it shows the right area A3 of my criteria range H2 to I3 and click OK. What that does is, it shows us just the matching records and then, I 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 how to use this, this is an old, old, old function.
One of the database functions DMIN =DMIN Let's see what it asked for. It asked for the database.
Okay well, that's this over here.
So, Ctrl+Shift right, down and a comma next it wants the field.
Well, I don't even remember if it asks for a number 1 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 as you said, that uses Mike's named range of criteria.
Kind of funny that even though I cut and paste that, the criteria is still pointing to the right area but, yeah, I guess it is.
All right,so let's press Enter.
There's 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 functions.
There you go, a lot of different ways to do it.
Want thank you for stopping by. Want to thank Mike for being a part of the dueling Excel podcast.
See you next time, for another netcast from MrExcel and Excel Is Fun.
