With a list of Products and a list of Dates, Bill's mission is to find the last order date for each product in the Dataset. Learn the use of MAXIF, changing Number Formats, and more in Episode #1518.
[Previously slated as Episode #1516]
[Previously slated as Episode #1516]
Transcript of the video:
MrExcel podcast is sponsored by the Excel pro weekly newsletter published by CFO and written by me.
Learn Excel form MrExcel podcast episode 1516, MAXIF?
Hey, welcome back to the mystery Excel netcast.
I'm Bill Jelen.
Here is today's question.
Today's question is we have a bunch of products listed here in column A and the order date I want to find the last order date for each product and I can't guarantee that the products will be sorted higher, you know the latest earliest, earliest to latest.
If it was latest earliest, more than I'm easy it'd just be a simple VLOOKUP, but can't guarantee that.
Now, the old DMAX function would do this, but I'm gonna try and do it a different way here.
I'm gonna ask for equal IF.
equal IF go look through all of these product names over here in column A.
I'll press F4, if that is equal to this product, then I want the date, the corresponding date from column B.
Press F4 there otherwise I want a zero.
Now, this is gonna work because I'm looking for the max.
If i was looking for the min and instead of a zero, there I wanna put a really large number.
All right! Now, this is going to return an array of answers in this case I have 18 rows you can't see down to row 19, but I have 18 rows of data.
So, it's going to return 18 answers and what I need to do is get the max from that.
So, the max becomes the wrapper function, WRAPPER, wrapper function and of course we have to do hold down ctrl + shift and press enter In order to get that answer, now, hey we recognize this anytime you get an answer in the 40,000 range 37,043 somewhere in that range and you're expecting a date, it's the right answer in the wrong format.
So, we go through and change that copy it down and now for each product we will see the latest order.
Now, let's just test this.
I'm gonna come here to product B I'm gonna put in a order date far in the future like 1/1/2013 and sure enough it is pulling it there, I could we do this with DMAX we could, but we'd have to have a separate criteria range for each of the products.
So, this is a little bit better while it's more confusing a lot of people aren't familiar the array formulas, it gets it all done in a single formula.
Oh! Hey, I wanna thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
Learn Excel form MrExcel podcast episode 1516, MAXIF?
Hey, welcome back to the mystery Excel netcast.
I'm Bill Jelen.
Here is today's question.
Today's question is we have a bunch of products listed here in column A and the order date I want to find the last order date for each product and I can't guarantee that the products will be sorted higher, you know the latest earliest, earliest to latest.
If it was latest earliest, more than I'm easy it'd just be a simple VLOOKUP, but can't guarantee that.
Now, the old DMAX function would do this, but I'm gonna try and do it a different way here.
I'm gonna ask for equal IF.
equal IF go look through all of these product names over here in column A.
I'll press F4, if that is equal to this product, then I want the date, the corresponding date from column B.
Press F4 there otherwise I want a zero.
Now, this is gonna work because I'm looking for the max.
If i was looking for the min and instead of a zero, there I wanna put a really large number.
All right! Now, this is going to return an array of answers in this case I have 18 rows you can't see down to row 19, but I have 18 rows of data.
So, it's going to return 18 answers and what I need to do is get the max from that.
So, the max becomes the wrapper function, WRAPPER, wrapper function and of course we have to do hold down ctrl + shift and press enter In order to get that answer, now, hey we recognize this anytime you get an answer in the 40,000 range 37,043 somewhere in that range and you're expecting a date, it's the right answer in the wrong format.
So, we go through and change that copy it down and now for each product we will see the latest order.
Now, let's just test this.
I'm gonna come here to product B I'm gonna put in a order date far in the future like 1/1/2013 and sure enough it is pulling it there, I could we do this with DMAX we could, but we'd have to have a separate criteria range for each of the products.
So, this is a little bit better while it's more confusing a lot of people aren't familiar the array formulas, it gets it all done in a single formula.
Oh! Hey, I wanna thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
