Alex O
Active Member
- Joined
- Mar 16, 2009
- Messages
- 345
- Office Version
- 365
- Platform
- 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
<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>
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
574643 | 36538498 | 1/4/2017 | OF DAYS. OK TO CHECK BACK WITH HER NEXT WEEK. |
574643 | 36538499 | 1/4/2017 | E/M CLT |
574643 | 36556860 | 1/6/2017 | PER CLT (A C) - LAST PAYMENT FOR $3681.98 |
574643 | 36556861 | 1/6/2017 | RECEIVED ON 7/29/16 |
574643 | 36574981 | 1/10/2017 | PER CLT - PER SPREADSHEET - BAL |
574643 | 36574982 | 1/10/2017 | 10808.86 |
574643 | 36574985 | 1/10/2017 | MADE PMT |
574643 | 36574993 | 1/10/2017 | (574643-1)PRINCIPAL ADJUSTMENT: ADJ DOWN $12,280.10 TO |
574643 | 36574994 | 1/10/2017 | READ $10,808.86 |
574643 | 36597615 | 1/12/2017 | CALLED #0621 - ELLEN VM LM |
574643 | 36597616 | 1/12/2017 | AUTOMATIC STATUS CHANGE FROM PPA TO 168. |
574643 | 36616023 | 1/16/2017 | CALLED #0621 - VM LM ELLEN |
574644 | 36484359 | 12/27/2016 | THE CHARLOTTE OBSERVER |