Max dates

[anneb_87]

Hi Experts!

I need to get the maximum DAY dates of the following clients
Client Max day dates
A 15
B
C


Here is the source data:
CLIENT CATEGORY DATE RECEIVED
A MEDICAL 7-NOV-19
A DENTAL 15-NOV-19
A VISION 15-NOV-19
B MEDICAL 15-NOV-19

 

[jasonb75]

Which edition of excel are you using?

Your sample appears to be in order by client name, then by date. Is the actaul source data sorted in any specific order? If yes, then what is that order?

 

[anneb_87]

Which edition of excel are you using?

Your sample appears to be in order by client name, then by date. Is the actaul source data sorted in any specific order? If yes, then what is that order?

It was just my example , there are really no specific order. Do you have any thoughts on how should I get the max day date? Thanks in advance!

 

[jasonb75]

Which edition of excel?

I do have several ideas, the lack of specific order has eliminated one of them, the rest depend on which version of excel you are using.

 

[anneb_87]

Which edition of excel?

I do have several ideas, the lack of specific order has eliminated one of them, the rest depend on which version of excel you are using.

Oh, it is Windows 10. Thank you in advance

 

[jasonb75]

Windows 10 is an operating system, not an excel edition.

Excel would be 2007, 2010, 2013, 2016, 2019 or office 365.

 

[anneb_87]

Oh sorry about this..its office 365

 

[jasonb75]

In that case, you should be able to use the maxifs function. You will need to change the sheet names and ranges as applicable to match your actual data.

=DAY(MAXIFS(SourceData!C:C,SourceData!A:A,OUTPUT!A2))

Note that the formula will return 0 for any lient with no data, you may wish to use conditional formatting to hide this.

 

[anneb_87]

Thanks Jason, Can you be more patient with me. Our source now is more complicated (for me). Can you help me again?? I got a really big data here

Here is what I need to fill in:

Client	Max Day Received
A	19​
B
C
D

Here is the source data:

Client	October Data	November Data	December Data
A	10/18/2019​	11/19/2019​	12/19/2019​
B
C
D

 

[jasonb75]

If I'm following that correctly then this formula should work as long as there is only 1 row of data per client.

The formula assumes that the output table is in A1:A5 and that the source table is in A1:D5 (adjust to suit as needed).

=IFERROR(AGGREGATE(14,6,DAY(INDEX(Source!$A:$D,MATCH(Output!$A2,Source!$A$1:$A$5,0),0)),1),"")

If you have more than 1 row for a client then it is going to be a little more difficult.