In todays podcast, I address the same question from podcast 614, but assume that the dataset can not be sorted. This requires a powerful array formula to find the most recent date for each customer. Episode 615 shows you how.
This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!
This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!
Transcript of the video:
Hey, welcome back to the MrExcel netcast.
I'm Bill Jelen.
Now, in yesterday's podcast we talked about a question that was sent separately by George and Alicia and I wanna revisit that question and they had a large data set with 20,000 rows, and they said we want to keep one record per customer.
That was George's question and Alicia said furthermore I want it to be the most recent record per customer and my simplifying assumption yesterday was, I was allowed to sort the data set but sometimes we're not gonna be allowed to sort the data set and so now, my solution is going to become a little bit more convoluted.
What I want to do, out here in column I, figure out the largest date for each customer.
So, I wanna build a formula that looks through all of the records and when it finds the largest value for this customer returns it.
So, here's how we're going to do that.
I'm going to say =MAX().
The MAX function of course will find the largest number within a series of numbers, but then I'm going to have an IF function. =IF(, this customer the customer in D2 is equal to the entire range of customers D2 to D564 and I'll make that b, F4 in other words if this customer matches the customer, then I want to see the date the corresponding date from C2 to C564, hit F4 there otherwise I want to see a 0.
So, what this is going to do for me, is it's going to give me in memory an array and for all the records let's say that match my current customer.
I'm going to have a date for all the other records I'm going to have a 0, and of course the max of all of those will be the largest record for that particular customer.
Now, in order to make this work it's a special formula, it's called an Array Formula.
We have to hold down the magic key stroke which is hold down control and shift while you hit enter.
Control and shift while you hit enter, and sure enough Excel will figure out that for, Bank of America the largest record is 12/28/2007 and as I copy this down, it should go through and figure out what the largest date is.
So, here for CitiGroup the last time that we sold to them was 12/15/2007, that Array Formula is doing all that magic and then Mckee Formula.
Now, is hey, let's see if this current date.
So, 7/17/2007 is equal to the max date most of the time we're gonna get False, but one record for every customer we will get True, and then just like yesterday will turn on the AutoFilter, and filter the most, the right most column to be true and we'll get just one record the most recent record for each customer plus the advantage is, we did not have to sort the data.
Much more difficult formula here to figure out the max.
Special kind of forming called an Array Formula, remember its control+shift+enter in order to make that work.
Hey, thanks for stopping by.
We'll see you next time for another netcast from MrExcel.
I'm Bill Jelen.
Now, in yesterday's podcast we talked about a question that was sent separately by George and Alicia and I wanna revisit that question and they had a large data set with 20,000 rows, and they said we want to keep one record per customer.
That was George's question and Alicia said furthermore I want it to be the most recent record per customer and my simplifying assumption yesterday was, I was allowed to sort the data set but sometimes we're not gonna be allowed to sort the data set and so now, my solution is going to become a little bit more convoluted.
What I want to do, out here in column I, figure out the largest date for each customer.
So, I wanna build a formula that looks through all of the records and when it finds the largest value for this customer returns it.
So, here's how we're going to do that.
I'm going to say =MAX().
The MAX function of course will find the largest number within a series of numbers, but then I'm going to have an IF function. =IF(, this customer the customer in D2 is equal to the entire range of customers D2 to D564 and I'll make that b, F4 in other words if this customer matches the customer, then I want to see the date the corresponding date from C2 to C564, hit F4 there otherwise I want to see a 0.
So, what this is going to do for me, is it's going to give me in memory an array and for all the records let's say that match my current customer.
I'm going to have a date for all the other records I'm going to have a 0, and of course the max of all of those will be the largest record for that particular customer.
Now, in order to make this work it's a special formula, it's called an Array Formula.
We have to hold down the magic key stroke which is hold down control and shift while you hit enter.
Control and shift while you hit enter, and sure enough Excel will figure out that for, Bank of America the largest record is 12/28/2007 and as I copy this down, it should go through and figure out what the largest date is.
So, here for CitiGroup the last time that we sold to them was 12/15/2007, that Array Formula is doing all that magic and then Mckee Formula.
Now, is hey, let's see if this current date.
So, 7/17/2007 is equal to the max date most of the time we're gonna get False, but one record for every customer we will get True, and then just like yesterday will turn on the AutoFilter, and filter the most, the right most column to be true and we'll get just one record the most recent record for each customer plus the advantage is, we did not have to sort the data.
Much more difficult formula here to figure out the max.
Special kind of forming called an Array Formula, remember its control+shift+enter in order to make that work.
Hey, thanks for stopping by.
We'll see you next time for another netcast from MrExcel.
