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.


[TABLE="width: 220"]
<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>[TR]
[TD="class: xl66, width: 64, bgcolor: transparent"]A[/TD]
[TD="class: xl66, width: 64, bgcolor: transparent"]B[/TD]
[TD="class: xl66, width: 97, bgcolor: transparent"]C[/TD]
[TD="class: xl66, width: 68, bgcolor: transparent"]D[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]Client[/TD]
[TD="class: xl68, bgcolor: transparent"]Check no[/TD]
[TD="class: xl68, bgcolor: transparent"]Day deposited[/TD]
[TD="class: xl67, bgcolor: transparent"]Date[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]0001[/TD]
[TD="class: xl68, bgcolor: transparent"]1[/TD]
[TD="class: xl69, bgcolor: transparent"]1/1/2014[/TD]
[TD="class: xl70, bgcolor: transparent, align: right"]1/16/2014[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]0001[/TD]
[TD="class: xl68, bgcolor: transparent"]2[/TD]
[TD="class: xl69, bgcolor: transparent"]1/6/2014[/TD]
[TD="class: xl70, bgcolor: transparent, align: right"]1/16/2014[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]0001[/TD]
[TD="class: xl68, bgcolor: transparent"]3[/TD]
[TD="class: xl69, bgcolor: transparent"]1/11/2014[/TD]
[TD="class: xl70, bgcolor: transparent, align: right"]1/16/2014[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]0001[/TD]
[TD="class: xl68, bgcolor: transparent"]4[/TD]
[TD="class: xl69, bgcolor: transparent"]1/16/2014[/TD]
[TD="class: xl70, bgcolor: transparent, align: right"]1/16/2014[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]0002[/TD]
[TD="class: xl68, bgcolor: transparent"]1[/TD]
[TD="class: xl69, bgcolor: transparent"]1/2/2014[/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]0002[/TD]
[TD="class: xl68, bgcolor: transparent"]2[/TD]
[TD="class: xl69, bgcolor: transparent"]1/7/2014[/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]0002[/TD]
[TD="class: xl68, bgcolor: transparent"]3[/TD]
[TD="class: xl69, bgcolor: transparent"]1/12/2014[/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]0003[/TD]
[TD="class: xl68, bgcolor: transparent"]1[/TD]
[TD="class: xl69, bgcolor: transparent"]1/3/2014[/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]0003[/TD]
[TD="class: xl68, bgcolor: transparent"]2[/TD]
[TD="class: xl69, bgcolor: transparent"]1/8/2014[/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]0003[/TD]
[TD="class: xl68, bgcolor: transparent"]3[/TD]
[TD="class: xl69, bgcolor: transparent"]1/13/2014[/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]0004[/TD]
[TD="class: xl68, bgcolor: transparent"]1[/TD]
[TD="class: xl69, bgcolor: transparent"]1/4/2014[/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]0004[/TD]
[TD="class: xl68, bgcolor: transparent"]2[/TD]
[TD="class: xl69, bgcolor: transparent"]1/9/2014[/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]0004[/TD]
[TD="class: xl68, bgcolor: transparent"]3[/TD]
[TD="class: xl69, bgcolor: transparent"]1/14/2014[/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]0004[/TD]
[TD="class: xl68, bgcolor: transparent"]4[/TD]
[TD="class: xl69, bgcolor: transparent"]1/19/2014[/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]0004[/TD]
[TD="class: xl68, bgcolor: transparent"]5[/TD]
[TD="class: xl69, bgcolor: transparent"]1/5/2014[/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]0005[/TD]
[TD="class: xl68, bgcolor: transparent"]1[/TD]
[TD="class: xl69, bgcolor: transparent"]1/10/2014[/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]0005[/TD]
[TD="class: xl68, bgcolor: transparent"]2[/TD]
[TD="class: xl69, bgcolor: transparent"]1/15/2014[/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]0005[/TD]
[TD="class: xl68, bgcolor: transparent"]3[/TD]
[TD="class: xl69, bgcolor: transparent"]1/20/2014[/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[/TR]
</tbody>[/TABLE]
 
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

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