Excel Formula to choose the highest value from rows with the same dates

[JEF13]

I have a spreadsheet that has between three and five entries for the same date. I want a formula that automatically chooses the highest value for the one day.

Here is a sample:
9/26/2023 79
9/26/2023 68
9/27/2023 76
9/27/2023 24
9/28/2023 77
9/28/2023 0
9/29/2023 82
9/30/2023 74
9/30/2023 20
10/1/2023 79
10/1/2023 0
10/1/2023 64


With the formula, the results would look like:

9/26/2023 79
9/27/2023 76
9/28/2023 77
9/29/2023 82
9/30/2023 74
10/1/2023 79

It would be helpful if the results could be on a new sheet.

 

[Fluff]

You need to use the maxifs function. MAXIFS Function

 

[kvsrinivasamurthy]

Data in A2:B13
In E2

=IFERROR(INDEX($A$2:$A$13,AGGREGATE(15,6,ROW($A$2:$A$13)/ISERROR(MATCH($A$2:$A$13,$E$1:$E1,0)),1)-ROW($A$1)),"")

In F2

=IF(E2="","",MAXIFS($B$2:$B$13,$A$2:$A$13,E2))

Copy down both till blank cell is seen.

 

[kvsrinivasamurthy]

Data in A2:B13
In E2
:

=IFERROR(AGGREGATE(15,6,$A$2:$A$13/ISERROR(MATCH($A$2:$A$13,$E$1:$E1,0)),1),"")

Format E2 for date.
In F2

=IF(E2="","",MAXIFS($B$2:$B$13,$A$2:$A$13,E2))

Copy down both till blank cell is seen.

 

[JEF13]

Thank you for the quick reply! I have data that start in 2 and ends in 1809. I changed the formula to go to 1809 but the numbers stop showing after cell 984.

The formulas read in row 985:

Column E: =IFERROR(INDEX($A$2:$A$1809,AGGREGATE(15,6,ROW($A$2:$A$1809)/ISERROR(MATCH($A$2:$A$1809,$E$1:$E984,0)),1)-ROW($A$1)),"")

Column F: =IF(E985="","",MAXIFS($B$2:$B$1809,$A$2:$A$1809,E985))

What can I do to have the values populate the rest of the data?

 

[kvsrinivasamurthy]

If Formula in E985 gives result empty, which means all the unique dates are listed, then stop dragging down else drag both the formulas further. If still problem is there upload a sample file in some site and give link here.
You can use formulas in Post#4 instead of Post#3.

 

[JEF13]

Thank you for stating the obvious.

All is working well.

 

[kvsrinivasamurthy]

Good. You are welcome.