ExcelBanter - Extract the maximum value and copy the entire row.

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - Extract the maximum value and copy the entire row. (https://www.excelbanter.com/excel-programming/340212-extract-maximum-value-copy-entire-row.html)

Extract the maximum value and copy the entire row.

This is my data. I will need to extract the rows for each ID that will
contain the maximum value of A and maximum value of B for that ID. I
will need to display them in another sheet.

ID Date A B
1 04/11/05 72515 12626
1 04/18/05 74289 5742
1 04/25/05 70410 1520
1 05/02/05 72536 5017
1 05/09/05 73724 8692
1 05/16/05 71348 8643
2 05/23/05 70807 8996
2 05/30/05 71125 10321
2 06/06/05 77999 16516
2 06/13/05 72273 8593
2 06/20/05 72018 6951
2 06/27/05 71319 6988
2 07/04/05 79540 11495
3 04/11/05 78988 1529
3 04/18/05 78430 1505
3 04/25/05 75618 1773
6 05/02/05 76432 1888
6 05/09/05 77610 948
6 05/16/05 77239 2503
6 05/23/05 76156 750
6 05/30/05 77547 864
6 06/06/05 80526 1649
6 06/13/05 81895 1863
6 06/20/05 77844 453
6 06/27/05 76878 1009
6 07/04/05 78346 909

What I need is:

ID Date-A Max-A Date-B Max-B
1 04/18/05 74289 04/11/05 12626
2 07/04/05 79540 06/06/05 16516
3
6
.....

Appreciate it.

Extract the maximum value and copy the entire row.

This will place the results on Sheet2 starting in row 2:

Sub GetList()
Dim ID As Integer
Dim ADate As Date
Dim BDate As Date
Dim AVal As Double
Dim BVal As Double
Dim eRow As Long
Dim i As Long
Dim RowC As Integer

eRow = Cells(Rows.Count, 1).End(xlUp).Row

ID = Cells(2, 1).Value
ADate = Cells(2, 2).Value
BDate = Cells(2, 2).Value
AVal = Cells(2, 3).Value
BVal = Cells(2, 4).Value
RowC = 2

For i = 3 To eRow + 1
If Cells(i, 1).Value < ID Then
With Sheets("Sheet2")
.Cells(RowC, 1).Value = ID
.Cells(RowC, 2).Value = ADate
.Cells(RowC, 3).Value = AVal
.Cells(RowC, 4).Value = BDate
.Cells(RowC, 5).Value = BVal
End With
RowC = RowC + 1
ID = Cells(i, 1).Value
ADate = Cells(i, 2).Value
BDate = Cells(i, 2).Value
AVal = Cells(i, 3).Value
BVal = Cells(i, 4).Value
Else
If Cells(i, 3).Value AVal Then
AVal = Cells(i, 3).Value
ADate = Cells(i, 2).Value
End If
If Cells(i, 4).Value BVal Then
BVal = Cells(i, 4).Value
BDate = Cells(i, 2).Value
End If
End If
Next

End Sub

Hope this helps
Rowan

Faye wrote:
This is my data. I will need to extract the rows for each ID that will
contain the maximum value of A and maximum value of B for that ID. I
will need to display them in another sheet.

ID Date A B
1 04/11/05 72515 12626
1 04/18/05 74289 5742
1 04/25/05 70410 1520
1 05/02/05 72536 5017
1 05/09/05 73724 8692
1 05/16/05 71348 8643
2 05/23/05 70807 8996
2 05/30/05 71125 10321
2 06/06/05 77999 16516
2 06/13/05 72273 8593
2 06/20/05 72018 6951
2 06/27/05 71319 6988
2 07/04/05 79540 11495
3 04/11/05 78988 1529
3 04/18/05 78430 1505
3 04/25/05 75618 1773
6 05/02/05 76432 1888
6 05/09/05 77610 948
6 05/16/05 77239 2503
6 05/23/05 76156 750
6 05/30/05 77547 864
6 06/06/05 80526 1649
6 06/13/05 81895 1863
6 06/20/05 77844 453
6 06/27/05 76878 1009
6 07/04/05 78346 909

What I need is:

ID Date-A Max-A Date-B Max-B
1 04/18/05 74289 04/11/05 12626
2 07/04/05 79540 06/06/05 16516
3
6
....

Appreciate it.

Extract the maximum value and copy the entire row.

Thank you very much. It works great.

Faye

Rowan wrote:
This will place the results on Sheet2 starting in row 2:

Sub GetList()
Dim ID As Integer
Dim ADate As Date
Dim BDate As Date
Dim AVal As Double
Dim BVal As Double
Dim eRow As Long
Dim i As Long
Dim RowC As Integer

eRow = Cells(Rows.Count, 1).End(xlUp).Row

ID = Cells(2, 1).Value
ADate = Cells(2, 2).Value
BDate = Cells(2, 2).Value
AVal = Cells(2, 3).Value
BVal = Cells(2, 4).Value
RowC = 2

For i = 3 To eRow + 1
If Cells(i, 1).Value < ID Then
With Sheets("Sheet2")
.Cells(RowC, 1).Value = ID
.Cells(RowC, 2).Value = ADate
.Cells(RowC, 3).Value = AVal
.Cells(RowC, 4).Value = BDate
.Cells(RowC, 5).Value = BVal
End With
RowC = RowC + 1
ID = Cells(i, 1).Value
ADate = Cells(i, 2).Value
BDate = Cells(i, 2).Value
AVal = Cells(i, 3).Value
BVal = Cells(i, 4).Value
Else
If Cells(i, 3).Value AVal Then
AVal = Cells(i, 3).Value
ADate = Cells(i, 2).Value
End If
If Cells(i, 4).Value BVal Then
BVal = Cells(i, 4).Value
BDate = Cells(i, 2).Value
End If
End If
Next

End Sub

Hope this helps
Rowan

Faye wrote:
This is my data. I will need to extract the rows for each ID that will
contain the maximum value of A and maximum value of B for that ID. I
will need to display them in another sheet.

ID Date A B
1 04/11/05 72515 12626
1 04/18/05 74289 5742
1 04/25/05 70410 1520
1 05/02/05 72536 5017
1 05/09/05 73724 8692
1 05/16/05 71348 8643
2 05/23/05 70807 8996
2 05/30/05 71125 10321
2 06/06/05 77999 16516
2 06/13/05 72273 8593
2 06/20/05 72018 6951
2 06/27/05 71319 6988
2 07/04/05 79540 11495
3 04/11/05 78988 1529
3 04/18/05 78430 1505
3 04/25/05 75618 1773
6 05/02/05 76432 1888
6 05/09/05 77610 948
6 05/16/05 77239 2503
6 05/23/05 76156 750
6 05/30/05 77547 864
6 06/06/05 80526 1649
6 06/13/05 81895 1863
6 06/20/05 77844 453
6 06/27/05 76878 1009
6 07/04/05 78346 909

What I need is:

ID Date-A Max-A Date-B Max-B
1 04/18/05 74289 04/11/05 12626
2 07/04/05 79540 06/06/05 16516
3
6
....

Appreciate it.

Extract the maximum value and copy the entire row.

Interestingly you can also do this without a macro. Assume your data is
in the range A1:D100 with headings in row 1.

In F2 enter the formula:

=INDEX($A$2:$A$100,SMALL(IF(MATCH($A$2:$A$100,$A$2 :$A$100,0)=ROW($A$1:INDIRECT(CHAR(65)&COUNTA($A$2: $A$100))),MATCH($A$2:$A$100,$A$2:$A$100,0),""),ROW ()-ROW($A$2:$A$100)+1))

This is an array formula and must be committed with Ctrl+Alt+Enter.

Copy the formula down for as many rows as you have unique IDs - it will
return #NUM errors if you go too far.

Enter: =SUMPRODUCT(MAX(($A$2:$A$100=F2)*($C$2:$C$100))) in H2
=SUMPRODUCT(MAX(($A$2:$A$100=F2)*($D$2:$D$100))) in J2
=SUMPRODUCT(($A$2:$A$100=F2)*($C$2:$C$100=H2),$B$2 :$B$100) in G2
=SUMPRODUCT(($A$2:$A$100=F2)*($D$2:$D$100=J2),$B$2 :$B$100) in I2

and copy all of these down.
Add some headings and you're done.

Note: the array formula to return unique IDs will return #NA errors if
the range you enter eg A2:A100 includes any blank rows so you will need
to adjust it to suit your situation.

Regards
Rowan

Faye wrote:
This is my data. I will need to extract the rows for each ID that will
contain the maximum value of A and maximum value of B for that ID. I
will need to display them in another sheet.

ID Date A B
1 04/11/05 72515 12626
1 04/18/05 74289 5742
1 04/25/05 70410 1520
1 05/02/05 72536 5017
1 05/09/05 73724 8692
1 05/16/05 71348 8643
2 05/23/05 70807 8996
2 05/30/05 71125 10321
2 06/06/05 77999 16516
2 06/13/05 72273 8593
2 06/20/05 72018 6951
2 06/27/05 71319 6988
2 07/04/05 79540 11495
3 04/11/05 78988 1529
3 04/18/05 78430 1505
3 04/25/05 75618 1773
6 05/02/05 76432 1888
6 05/09/05 77610 948
6 05/16/05 77239 2503
6 05/23/05 76156 750
6 05/30/05 77547 864
6 06/06/05 80526 1649
6 06/13/05 81895 1863
6 06/20/05 77844 453
6 06/27/05 76878 1009
6 07/04/05 78346 909

What I need is:

ID Date-A Max-A Date-B Max-B
1 04/18/05 74289 04/11/05 12626
2 07/04/05 79540 06/06/05 16516
3
6
....

Appreciate it.

Extract the maximum value and copy the entire row.

You're welcome.

Faye wrote:
Thank you very much. It works great.

Faye

Rowan wrote:

This will place the results on Sheet2 starting in row 2:

Sub GetList()
Dim ID As Integer
Dim ADate As Date
Dim BDate As Date
Dim AVal As Double
Dim BVal As Double
Dim eRow As Long
Dim i As Long
Dim RowC As Integer

eRow = Cells(Rows.Count, 1).End(xlUp).Row

ID = Cells(2, 1).Value
ADate = Cells(2, 2).Value
BDate = Cells(2, 2).Value
AVal = Cells(2, 3).Value
BVal = Cells(2, 4).Value
RowC = 2

For i = 3 To eRow + 1
If Cells(i, 1).Value < ID Then
With Sheets("Sheet2")
.Cells(RowC, 1).Value = ID
.Cells(RowC, 2).Value = ADate
.Cells(RowC, 3).Value = AVal
.Cells(RowC, 4).Value = BDate
.Cells(RowC, 5).Value = BVal
End With
RowC = RowC + 1
ID = Cells(i, 1).Value
ADate = Cells(i, 2).Value
BDate = Cells(i, 2).Value
AVal = Cells(i, 3).Value
BVal = Cells(i, 4).Value
Else
If Cells(i, 3).Value AVal Then
AVal = Cells(i, 3).Value
ADate = Cells(i, 2).Value
End If
If Cells(i, 4).Value BVal Then
BVal = Cells(i, 4).Value
BDate = Cells(i, 2).Value
End If
End If
Next

End Sub

Hope this helps
Rowan

Faye wrote:

This is my data. I will need to extract the rows for each ID that will
contain the maximum value of A and maximum value of B for that ID. I
will need to display them in another sheet.

ID Date A B
1 04/11/05 72515 12626
1 04/18/05 74289 5742
1 04/25/05 70410 1520
1 05/02/05 72536 5017
1 05/09/05 73724 8692
1 05/16/05 71348 8643
2 05/23/05 70807 8996
2 05/30/05 71125 10321
2 06/06/05 77999 16516
2 06/13/05 72273 8593
2 06/20/05 72018 6951
2 06/27/05 71319 6988
2 07/04/05 79540 11495
3 04/11/05 78988 1529
3 04/18/05 78430 1505
3 04/25/05 75618 1773
6 05/02/05 76432 1888
6 05/09/05 77610 948
6 05/16/05 77239 2503
6 05/23/05 76156 750
6 05/30/05 77547 864
6 06/06/05 80526 1649
6 06/13/05 81895 1863
6 06/20/05 77844 453
6 06/27/05 76878 1009
6 07/04/05 78346 909

What I need is:

ID Date-A Max-A Date-B Max-B
1 04/18/05 74289 04/11/05 12626
2 07/04/05 79540 06/06/05 16516
3
6
....

Appreciate it.

Extract the maximum value and copy the entire row.

Hi Faye

Try changing the first line from:
Dim ID As Integer
to
Dim ID As Long

Regards
Rowan