Max dates

anneb_87

New Member
Joined
Jun 13, 2018
Messages
49
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
 

Some videos you may like

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
9,023
Office Version
2019
Platform
Windows
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

New Member
Joined
Jun 13, 2018
Messages
49
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

Well-known Member
Joined
Dec 30, 2008
Messages
9,023
Office Version
2019
Platform
Windows
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

New Member
Joined
Jun 13, 2018
Messages
49
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

Well-known Member
Joined
Dec 30, 2008
Messages
9,023
Office Version
2019
Platform
Windows
Windows 10 is an operating system, not an excel edition.

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

anneb_87

New Member
Joined
Jun 13, 2018
Messages
49
Oh sorry about this..its office 365 :)
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
9,023
Office Version
2019
Platform
Windows
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

New Member
Joined
Jun 13, 2018
Messages
49
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:

ClientMax Day Received
A
19​
B
C
D


Here is the source data:
ClientOctober DataNovember DataDecember Data
A
10/18/2019​
11/19/2019​
12/19/2019​
B
C
D
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
9,023
Office Version
2019
Platform
Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,095,682
Messages
5,445,939
Members
405,370
Latest member
Theglyde

This Week's Hot Topics

Top