Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Extract the maximum value and copy the entire row.
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 12
Default 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.

Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 88
Default 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.

Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 12
Default 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.


Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 88
Default 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.

Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 88
Default 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.



Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 88
Default 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
Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Copy an entire sheet Gynandra Excel Discussion (Misc queries) 1 June 28th 06 04:31 AM
Copy entire row if... Sam Excel Programming 7 November 25th 04 06:16 PM
how to copy entire row into next row john_liu Excel Programming 2 September 22nd 04 02:17 PM
Code to copy range vs Copy Entire Worksheet - can't figure it out Mike Taylor Excel Programming 1 April 15th 04 08:34 PM
Copy entire Worksheet how? Stormin' German Excel Programming 2 February 4th 04 12:26 AM


All times are GMT +1. The time now is 06:59 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement