How to use countifs to return a max date from a list of dates meeting a certain critera?

[Captain McFunk]

Hey all, I had this answered last week that I could reference back to but it appears it was wiped out wit the db attack, so i'm posting again, i apologize for having to have it answered twice!

I have a list of patients that have multiple orders listed by date. I'm trying to find what is the most recent order date and corresponding row data for every customer id#. My table looks a little like this:

CustomerID	OrderDate
12345	10/25/16
12345	11/25/16
12345	12/25/16
67891	9/25/16
67891	10/25/16
67891	11/25/16
67891	12/25/16

<tbody>
</tbody>

I'm needing to be able to identify and filter to show just the rows for each customerid that have the most recent order date. I remember the solution having something to do with a countifs statement, but i tried a couple of times and couldn't get there. I would greatly appreciate some help!!!

 

[godsaaint]

I wouldn't bother with a formula (unless for some other reason you want to). Sort the "OrderDate" column by most Newest to Oldest and then select the entire table and delete duplicate values based on column "CustomerID". Excel will delete duplicates starting from the bottom, thus, the only remaining value for each customerID is going to be the most recent.

 

[Captain McFunk]

I wouldn't bother with a formula (unless for some other reason you want to). Sort the "OrderDate" column by most Newest to Oldest and then select the entire table and delete duplicate values based on column "CustomerID". Excel will delete duplicates starting from the bottom, thus, the only remaining value for each customerID is going to be the most recent.

Okay, I realized a slight issue when trying this, there is a piece of info i left out, my apologies! Each sales order has an item on it -- There could be a patient that receives 3 items on the same date -- They each have their own separate row -- I need to be able to see all 3 rows for that date for that patient, if that makes sense? I guess the table is more like:

CustomerID	OrderDate	ItemID
12345	10/25/16	Item1
12345	11/25/16	Item1
12345	12/25/16	Item1
67891	9/25/16	Item3
67891	10/25/16	Item3
67891	11/25/16	Item3
67891	11/25/16	Item7

<tbody>
</tbody>

For this example, i need a formula that would return me this info:

CustomerID	OrderDate	ItemID
12345	12/25/16	Item1
67891	11/25/16	Item3
67891	11/25/16	Item7

<tbody>
</tbody>

 

[AliGW]

Very easy with a formula, especially if you don't want to delete records! C1 contains the customer ID you want to query:

=MAX(IF(A2:A8=C1,B2:B8))

confirmed by pressing CTRL+SHIFT+ENTER (not just ENTER) which will automatically insert curly brackets {} around the formula (don't try to type these yourself - it won't work).

EDIT: Just read your latest addition - now it is a completely different query!!!

 

[Captain McFunk]

maybe something where, instead of deleting all duplicates, it would return all rows associated with the "newest" orderdate for each customerID if that makes sense?

 

[Captain McFunk]

haha, all good, I just tried this, and this did give me the most recent order date for a given customerid, but just put it in a column I selected for ALL rows for a given patient ID. Thank you for helping though, looking forward to your next option!

 

[Captain McFunk]

Here is the actual date with some edits made for HIPAA reasons....

Patient ID	Patient Last Name	Patient First Name	Sales Order Detail Item Name	Sales Order Detail Qty	Delivery Actual date
10228	LastName	FirstName	BOOST KIDS ESSENTIALS - CHOCOLATE	1	11/17/2016
10228	LastName	FirstName	BOOST KIDS ESSENTIALS - STRAWBERRY	1	11/17/2016
10228	LastName	FirstName	BOOST KIDS ESSENTIALS - VANILLA	2	11/17/2016
10228	LastName	FirstName	BOOST KIDS ESSENTIALS - CHOCOLATE	1	10/17/2016
10228	LastName	FirstName	BOOST KIDS ESSENTIALS - STRAWBERRY	1	10/17/2016
10228	LastName	FirstName	BOOST KIDS ESSENTIALS - VANILLA	2	10/17/2016
10228	LastName	FirstName	BOOST KIDS ESSENTIALS - CHOCOLATE	1	9/17/2016
10228	LastName	FirstName	BOOST KIDS ESSENTIALS - STRAWBERRY	1	9/17/2016
10228	LastName	FirstName	BOOST KIDS ESSENTIALS - VANILLA	2	9/17/2016
10228	LastName	FirstName	BOOST KIDS ESSENTIALS - CHOCOLATE	1	8/17/2016
10228	LastName	FirstName	BOOST KIDS ESSENTIALS - STRAWBERRY	1	8/17/2016
10228	LastName	FirstName	BOOST KIDS ESSENTIALS - VANILLA	2	8/17/2016
10228	LastName	FirstName	BOOST KIDS ESSENTIALS - CHOCOLATE	1	7/17/2016
10228	LastName	FirstName	BOOST KIDS ESSENTIALS - STRAWBERRY	1	7/17/2016
10228	LastName	FirstName	BOOST KIDS ESSENTIALS - VANILLA	2	7/17/2016
10228	LastName	FirstName	BOOST KIDS ESSENTIALS - CHOCOLATE	1	6/17/2016
10228	LastName	FirstName	BOOST KIDS ESSENTIALS - STRAWBERRY	1	6/17/2016
10228	LastName	FirstName	BOOST KIDS ESSENTIALS - VANILLA	2	6/17/2016
10228	LastName	FirstName	BOOST KIDS ESSENTIALS - CHOCOLATE	1	5/17/2016
10228	LastName	FirstName	BOOST KIDS ESSENTIALS - STRAWBERRY	1	5/17/2016
10228	LastName	FirstName	BOOST KIDS ESSENTIALS - VANILLA	2	5/17/2016
10228	LastName	FirstName	BOOST KIDS ESSENTIALS - CHOCOLATE	1	4/17/2016
10228	LastName	FirstName	BOOST KIDS ESSENTIALS - STRAWBERRY	1	4/17/2016
10228	LastName	FirstName	BOOST KIDS ESSENTIALS - VANILLA	2	4/17/2016
10228	LastName	FirstName	BOOST KIDS ESSENTIALS - CHOCOLATE	1	3/17/2016
10228	LastName	FirstName	BOOST KIDS ESSENTIALS - STRAWBERRY	1	3/17/2016
10228	LastName	FirstName	BOOST KIDS ESSENTIALS - VANILLA	2	3/17/2016
10228	LastName	FirstName	BOOST KIDS ESSENTIALS - CHOCOLATE	1	2/17/2016
10228	LastName	FirstName	BOOST KIDS ESSENTIALS - STRAWBERRY	1	2/17/2016
10228	LastName	FirstName	BOOST KIDS ESSENTIALS - VANILLA	2	2/17/2016
10228	LastName	FirstName	BOOST KIDS ESSENTIALS - CHOCOLATE	1	1/17/2016
10228	LastName	FirstName	BOOST KIDS ESSENTIALS - STRAWBERRY	1	1/17/2016
10228	LastName	FirstName	BOOST KIDS ESSENTIALS - VANILLA	2	1/17/2016

<tbody>
</tbody>

 

[Captain McFunk]

In this scenario, i'd want it to look by patient id, and for all instances of each patient ID in a list of over 2,000 items, return all rows associated with the newest date, so in the above, it would look for the first patient id, 10228, and show me JUST the lines associated with the most recent date of 11/17/2016. How could I go about that? I'm pretty sure it's a countifs statement...

 

[Captain McFunk]

in this scenario, i'd want it to look by patient id, and for all instances of each patient id in a list of over 2,000 items, return all rows associated with the newest date, so in the above, it would look for the first patient id, 10228, and show me just the lines associated with the most recent date of 11/17/2016. How could i go about that? I'm pretty sure it's a countifs statement...

tttop

 

[AliGW]

It's been just half an hour since you updated the thread - patience, please! Please remember that we are all helping here voluntarily and in our free time!!!

You are going to need an INDEX Match array formula to do what you want. I have to go out now, but if nobody has answered by the time I get back, then I shall propose a solution.