Determining the maximum value from same-category items in a many-category list

[mooveengsuun]

Hello,
I'm a new user to this forum, my signing up prompted by a major difficulty I'm encountering with Excel, and I'd be very grateful for help!

To simplify things as much as possible, this is a log book in which:

• Column A is filled with client ID numbers. A client has only one ID number.

• Column B is filled with expiry dates for their subscription to a service.

• I want Column C to state whether the expiry date is the latest for this particular client. The logbook lists all subscriptions paid over the years for each client. This means that in the logbook, a line will be devoted to client "X" when it suscribed in 2008 for a year, and fifty rows below another one will be devoted to its subscription in 2010 for two years this time. The latest expiry date overrides any previous expiry dates. Therefore this cell is intended to state whether the line is still relevant in the logbook: I basically want to write "1" in the 2010 subscription line for client "X", and "0" for its 2008 subscription line.

I will then use the whole range to make a pivot table, and filter all the irrelevant (overridden expiry dates, for which column C states 0) out of the pivot table.

Do you know of any formula that I could use in column C? The real difficulty for me is comparing one expiry date to multiple other expiry dates that have in common a single ID number.

I hope this is clear enough, please forgive my rusty English -- many thanks in advance for any suggestions / solutions !

 

[Aladin Akyurek]

Given the following ID/Date pairs in A:B...

1,1-Apr-10
2,1-Apr-09
1,1-Apr-11
2,1-Mar-11

What is the desired result?

 

[mooveengsuun]

Thanks for your quick post!
I added a line to make it as clear as possible:

1,1-Apr-10,0 because 1-Apr-10<max(1-apr-10,1-apr-11,1-apr-12)> < </max(1-apr-10,1-apr-11,1-apr-12)>max(1-Apr-10,1-Apr-11,1-Apr-12)
<max(1-apr-10,1-apr-11,1-apr-12)> 2,1-Apr-09,0 because 1-Apr-09 < </max(1-apr-10,1-apr-11,1-apr-12)>max(1-Apr-09,1-Mar-11)
<max(1-apr-10,1-apr-11,1-apr-12)><max(1-apr-09,1-mar-11)> 1,1-Apr-11,0 ... (same logic as first line)
2,1-Mar-11,1 because 1-Mar-11=max(1-Apr-09,1-Mar-11)
1,1-Apr-12,1 because 1-Apr-12=max(1-Apr-10,1-Apr-11,1-Apr-12)</max(1-apr-09,1-mar-11)></max(1-apr-10,1-apr-11,1-apr-12)>

 

[Aladin Akyurek]

Thanks for your quick post!
I added a line to make it as clear as possible:

1,1-Apr-10,0 because 1-Apr-10<MAX(1-APR-10,1-APR-11,1-APR-12)> < </MAX(1-APR-10,1-APR-11,1-APR-12)>max(1-Apr-10,1-Apr-11,1-Apr-12)
<MAX(1-APR-10,1-APR-11,1-APR-12)>2,1-Apr-09,0 because 1-Apr-09 < </MAX(1-APR-10,1-APR-11,1-APR-12)>max(1-Apr-09,1-Mar-11)
<MAX(1-APR-10,1-APR-11,1-APR-12)><MAX(1-APR-09,1-MAR-11)>1,1-Apr-11,0 ... (same logic as first line)
2,1-Mar-11,1 because 1-Mar-11=max(1-Apr-09,1-Mar-11)
1,1-Apr-12,1 because 1-Apr-12=max(1-Apr-10,1-Apr-11,1-Apr-12)</MAX(1-APR-09,1-MAR-11)></MAX(1-APR-10,1-APR-11,1-APR-12)>

<TABLE style="WIDTH: 136pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=182><COLGROUP><COL style="WIDTH: 25pt; mso-width-source: userset; mso-width-alt: 1194" width=34><COL style="WIDTH: 54pt; mso-width-source: userset; mso-width-alt: 2560" width=72><COL style="WIDTH: 57pt; mso-width-source: userset; mso-width-alt: 2702" width=76><TBODY><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 25pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68 height=19 width=34>Id</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 54pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" id=td_post_2703091 class=xl68 width=72>Date</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 57pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68 width=76>Max Date</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19 align=right>1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 align=right>1-Apr-10</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl69>0</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=19 align=right>2</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 align=right>1-Apr-09</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl69>0</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=19 align=right>1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 align=right>1-Apr-11</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl69>0</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=19 align=right>2</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 align=right>1-Mar-11</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl69>1</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=19 align=right>1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 align=right>1-Apr-12</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl69>1</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65> </TD></TR></TBODY></TABLE>

C2, control+shift+enter, not just enter, and copy down:

=(B2=MAX(IF($A$2:$A$6=A2,$B$2:$B$6)))+0

With some control added:

=IF(ISNUMBER(1/(A2*B2)),(B2=MAX(IF($A$2:$A$6=A2,$B$2:$B$6)))+0,"")

which also needs control+shift+enter.

 

[mooveengsuun]

Wow... simply beautiful. Took me a while to absorb it, and a bit longer to admire it.

Many, many thanks.

 

[Aladin Akyurek]

Wow... simply beautiful. Took me a while to absorb it, and a bit longer to admire it.

Many, many thanks.

You are welcome. Thanks for providing feedback.