Formula Edit to Return Max Value

Alex O

Active Member
Joined
Mar 16, 2009
Messages
345
Office Version
  1. 365
Platform
  1. Windows
I'm using the formula below to return a list of comments from Sheet2 (Comments) in my workbook, and it's working flawlessly. But I'm stumped on how I get have it return the most recent date. So using the example below, for client 574643 it would only return the 1/16/2017 entry.

Any help is appreciated!


=IF(ROWS(Comments!$A$5:B5)>COUNTIF(Comments!$A$5:$A$2102,Report!$BB$11),"",INDEX(Comments!$E$5:$E$2102,SMALL(INDEX((Comments!$A$5:$A$2102=Report!$BB$11)*(ROW(Comments!$A$5:$A$2102)-ROW(Comments!$A$5)+1),),COUNTIF(Comments!$A$5:$A$2102,"<>"&Report!$BB$11)+ROWS(Comments!$A$5:B5))))

A B C D
574643365384981/4/2017OF DAYS. OK TO CHECK BACK WITH HER NEXT WEEK.
574643365384991/4/2017E/M CLT
574643365568601/6/2017PER CLT (A C) - LAST PAYMENT FOR $3681.98
574643365568611/6/2017RECEIVED ON 7/29/16
574643365749811/10/2017PER CLT - PER SPREADSHEET - BAL
574643365749821/10/201710808.86
574643365749851/10/2017MADE PMT
574643365749931/10/2017(574643-1)PRINCIPAL ADJUSTMENT: ADJ DOWN $12,280.10 TO
574643365749941/10/2017READ $10,808.86
574643365976151/12/2017CALLED #0621 - ELLEN VM LM
574643365976161/12/2017AUTOMATIC STATUS CHANGE FROM PPA TO 168.
574643366160231/16/2017CALLED #0621 - VM LM ELLEN
5746443648435912/27/2016THE CHARLOTTE OBSERVER
<colgroup><col width="49" style="width: 37pt; mso-width-source: userset; mso-width-alt: 1792;"> <col width="63" style="width: 47pt; mso-width-source: userset; mso-width-alt: 2304;"> <col width="71" style="width: 53pt; mso-width-source: userset; mso-width-alt: 2596;"> <col width="401" style="width: 301pt; mso-width-source: userset; mso-width-alt: 14665;"> <tbody> </tbody>
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
Maybe something like this?


Excel 2010
ABCD
1574643365384981/4/2017OF DAYS. OK TO CHECK BACK WITH HER NEXT WEEK.
2574643365384991/4/2017E/M CLT
3574643365568601/6/2017PER CLT (A C) - LAST PAYMENT FOR $3681.98
4574643365568611/6/2017RECEIVED ON 7/29/16
5574643365749811/10/2017PER CLT - PER SPREADSHEET - BAL
6574643365749821/10/201710808.86
7574643365749851/10/2017MADE PMT
8574643365749931/10/2017(574643-1)PRINCIPAL ADJUSTMENT: ADJ DOWN $12,280.10 TO
9574643365749941/10/2017READ $10,808.86
10574643365976151/12/2017CALLED #0621 - ELLEN VM LM
11574643365976161/12/2017AUTOMATIC STATUS CHANGE FROM PPA TO 168.
12574643366160231/16/2017CALLED #0621 - VM LM ELLEN
135746443648435912/27/2016THE CHARLOTTE OBSERVER
14
15ClientComment
16574643CALLED #0621 - VM LM ELLEN
Sheet2
Cell Formulas
RangeFormula
B16{=INDEX(D1:D13,MATCH(A16&MAX(IF(A1:A13=A16,C1:C13)),A1:A13&C1:C13))}
Press CTRL+SHIFT+ENTER to enter array formulas.
 
Upvote 0
A
B
C
D
E
F
G
H
I
1
574643​
36538498​
1/4/2017​
OF DAYS. OK TO CHECK BACK WITH HER NEXT WEEK.max DATE
2
574643​
36538499​
1/4/2017​
E/M CLT
574643​
1/16/2017​
3
574643​
36556860​
1/6/2017​
PER CLT (A C) - LAST PAYMENT FOR $3681.98
574644​
12/27/2016​
4
574643​
36556861​
1/6/2017​
RECEIVED ON 7/29/16
5
574643​
36574981​
1/10/2017​
PER CLT - PER SPREADSHEET - BAL
6
574643​
36574982​
1/10/2017​
10808.86​
7
574643​
36574985​
1/10/2017​
MADE PMT
8
574643​
36574993​
1/10/2017​
(574643-1)PRINCIPAL ADJUSTMENT: ADJ DOWN $12,280.10 TO
9
574643​
36574994​
1/10/2017​
READ $10,808.86
10
574643​
36597615​
1/12/2017​
CALLED #0621 - ELLEN VM LM
11
574643​
36597616​
1/12/2017​
AUTOMATIC STATUS CHANGE FROM PPA TO 168.
12
574643​
36616023​
1/16/2017​
CALLED #0621 - VM LM ELLEN
13
574644​
36484359​
12/27/2016​
THE CHARLOTTE OBSERVER

<tbody>
</tbody>



I2=
MAX(IF($A$1:$A$13=$H2,$C$1:$C$13)) control+shift +enter copy down

format coulumn I as date
 
Upvote 0
It works perfect for returning the max date, but it returns 0 when trying to retrieve the comment

=MAX(IF(Comments!$A$5:$A$2102=$BB$11,Comments!$D$5:$D$2102)))



A

B

C

D

E

F

G

H

I

1

574643​

36538498​

1/4/2017​
OF DAYS. OK TO CHECK BACK WITH HER NEXT WEEK.
max DATE

2

574643​

36538499​

1/4/2017​
E/M CLT

574643​

1/16/2017​

3

574643​

36556860​

1/6/2017​
PER CLT (A C) - LAST PAYMENT FOR $3681.98

574644​

12/27/2016​

4

574643​

36556861​

1/6/2017​
RECEIVED ON 7/29/16

5

574643​

36574981​

1/10/2017​
PER CLT - PER SPREADSHEET - BAL

6

574643​

36574982​

1/10/2017​

10808.86​

7

574643​

36574985​

1/10/2017​
MADE PMT

8

574643​

36574993​

1/10/2017​
(574643-1)PRINCIPAL ADJUSTMENT: ADJ DOWN $12,280.10 TO

9

574643​

36574994​

1/10/2017​
READ $10,808.86

10

574643​

36597615​

1/12/2017​
CALLED #0621 - ELLEN VM LM

11

574643​

36597616​

1/12/2017​
AUTOMATIC STATUS CHANGE FROM PPA TO 168.

12

574643​

36616023​

1/16/2017​
CALLED #0621 - VM LM ELLEN

13

574644​

36484359​

12/27/2016​
THE CHARLOTTE OBSERVER

<tbody>
</tbody>



I2=
MAX(IF($A$1:$A$13=$H2,$C$1:$C$13)) control+shift +enter copy down

format coulumn I as date
 
Upvote 0

Forum statistics

Threads
1,213,538
Messages
6,114,218
Members
448,554
Latest member
Gleisner2

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