Max Date in a Data set

rhouston08

New Member
Joined
Nov 25, 2013
Messages
21
I would like to retrieve the max number in a dataset. For example, in the table below, I would like to find the max check no and return the date associated with that check no, and I would like the cells in column D to show the date for the max set.


ABCD
ClientCheck noDay depositedDate
000111/1/20141/16/2014
000121/6/20141/16/2014
000131/11/20141/16/2014
000141/16/20141/16/2014
000211/2/2014
000221/7/2014
000231/12/2014
000311/3/2014
000321/8/2014
000331/13/2014
000411/4/2014
000421/9/2014
000431/14/2014
000441/19/2014
000451/5/2014
000511/10/2014
000521/15/2014
000531/20/2014

<colgroup><col style="width: 48pt;" width="64"> <col style="width: 48pt;" width="64"> <col style="width: 73pt; mso-width-source: userset; mso-width-alt: 3547;" width="97"> <col style="width: 51pt; mso-width-source: userset; mso-width-alt: 2486;" width="68"> <tbody>
</tbody>
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
Try this: =VLOOKUP(MAX($B$2:$B$19),$B$2:$C$19,2,FALSE)


EDIT: Ignore this solution. I missed the part about Max per client.
 
Last edited:
Upvote 0
Confirm the formula with control shift enter and then copy down.

In D2
Code:
=MAX(IF($A$2:$A$19=A2,$C$2:$C$19))


Excel 2010
ABCD
1ClientCheck noDay depositedDate
2111/1/20141/16/2014
3121/6/20141/16/2014
4131/11/20141/16/2014
5141/16/20141/16/2014
6211/2/20141/12/2014
7221/7/20141/12/2014
8231/12/20141/12/2014
9311/3/20141/13/2014
10321/8/20141/13/2014
11331/13/20141/13/2014
12411/4/20141/19/2014
13421/9/20141/19/2014
14431/14/20141/19/2014
15441/19/20141/19/2014
16451/5/20141/19/2014
17511/10/20141/20/2014
18521/15/20141/20/2014
19531/20/20141/20/2014
Sheet1
Cell Formulas
RangeFormula
D2{=MAX(IF($A$2:$A$19=A2,$C$2:$C$19))}
Press CTRL+SHIFT+ENTER to enter array formulas.
 
Upvote 0
I like skywriter's solution, getting the max date only, unless there is a possibility that checks can be deposited out of order. Consider, if check 4 is deposited after check 5 what date would you want returned?
 
Upvote 0

Forum statistics

Threads
1,206,756
Messages
6,074,758
Members
446,084
Latest member
WalmitAal

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top