Select Max Value for ID

F

Forestq

Active Member
Joined
May 9, 2010
Messages
482
Team,
I need to write macro, which will be sorting two columns:

- first is ID (ID could be this some like here):
120
120
120
131
131
132
140
140
...
..
.

- second is status: Published or draft, and then

- from third columns (due date) select the max data for ID:

***example (columns with simple data)***
ID ---------- status -------------due date
120 -------- Published -------- 31-Dec-2008
120 -------- Published -------- 11-Sep-2011
120 -------- draft -------------01-Jan-2010
131 -------- Published -------- 08-Sep-2011
131 -------- Published -------- 14-Dec-2011

result should be:
120 - 11-Sep-2011
131 - 14-Dec-2011
All results should be paste in sheet2.

I just now how sort data:
Code: 
  ActiveWorkbook.Worksheets("Sheet1").AutoFilter.Sort.SortFields.Clear
 
  ActiveWorkbook.Worksheets("Sheet1").AutoFilter.Sort.SortFields.Add Key:=Range _
        ("A2:A2800"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
 
  ActiveWorkbook.Worksheets("Sheet1").AutoFilter.Sort.SortFields.Add Key:=Range _
        ("E2:E2800"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
        xlSortNormal
 
  With ActiveWorkbook.Worksheets("Sheet1").AutoFilter.Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
  End With


Please help me with this case.

regards,
PvK
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
Sort by date Sort by votes
I know that I can use PivotTable to resolve this issue (this is god, clear concept, I do it myself).

But still I want do it in other way. This issue, problem worried me from long time...
 
Upvote 0
Look at this sample.

What I have done is created a small table with some codes (as numbers) and some dates.

Excel Workbook
KL
1NumberDate
212021/09/2011
312013/09/2011
412001/10/2011
523010/12/2011
623002/11/2011
723009/08/2011
Sheet1
Click to expand...

Next what I have done is used an Advanced Filter to get a single list of the codes. Then used an Array formula to find the latest date.

Excel Workbook
OP
1NumberDates
212001/10/2011
323010/12/2011
Sheet1
Click to expand...

Whilst creating this I recorded the actions in a Macro and it came up with this code: (Its possibly not the best but it gets you on your way.)

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> mcrList()<br><SPAN style="color:#007F00">'</SPAN><br><SPAN style="color:#007F00">' mcrList Macro</SPAN><br><SPAN style="color:#007F00">'</SPAN><br><br><SPAN style="color:#007F00">'</SPAN><br>    Range("K1:K7").Select<br>    Range("K1:K7").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("O1" _<br>        ), Unique:=<SPAN style="color:#00007F">True</SPAN><br>    Range("P2").Select<br>    Range("P2").FormulaArray = "=MAX(IF(RC[-5]:R[5]C[-5]=RC[-1],RC[-4]:R[5]C[-4]))"<br>    Selection.AutoFill Destination:=Range("P2:P3")<br>    Range("P2:P3").Select<br>    Range("P1") = "Date"<br>    <br>    <br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>
Click to expand...
 
Upvote 0
Just streamlined Trevor's code

Code: 
Sub mcrList()
    Range("K1:K7").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("O1" _
        ), Unique:=True
    Range("P1") = "Date"
    With Range("P2")
        .FormulaArray = "=MAX(IF(K2:K7=O2,L2:L7))"
        .AutoFill Destination:=Range("P2:P3")
    End With
    Range("P2:P3").NumberFormat = "dd/mm/yyyy"
End Sub

Biz
 
Upvote 0
Forestq said:
Team,
I need to write macro, which will be sorting two columns:

- first is ID (ID could be this some like here):
120
120
120
131
131
132
140
140
...
..
.

- second is status: Published or draft, and then

- from third columns (due date) select the max data for ID:

***example (columns with simple data)***
ID ---------- status -------------due date
120 -------- Published -------- 31-Dec-2008
120 -------- Published -------- 11-Sep-2011
120 -------- draft -------------01-Jan-2010
131 -------- Published -------- 08-Sep-2011
131 -------- Published -------- 14-Dec-2011

result should be:
120 - 11-Sep-2011
131 - 14-Dec-2011
All results should be paste in sheet2.

I just now how sort data:
Code: 
  ActiveWorkbook.Worksheets("Sheet1").AutoFilter.Sort.SortFields.Clear
 
  ActiveWorkbook.Worksheets("Sheet1").AutoFilter.Sort.SortFields.Add Key:=Range _
        ("A2:A2800"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
 
  ActiveWorkbook.Worksheets("Sheet1").AutoFilter.Sort.SortFields.Add Key:=Range _
        ("E2:E2800"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
        xlSortNormal
 
  With ActiveWorkbook.Worksheets("Sheet1").AutoFilter.Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
  End With


Please help me with this case.

regards,
PvK
Click to expand...
see code placed in this thread
http://www.mrexcel.com/forum/showthread.php?t=575808#4
should help you. regards
 
Upvote 0
Biz, Trevor G,

thanks for help, it's work god.
Now I changed code to select all column with ID and Data. What should I do with

Code: 
.AutoFill Destination:=Range("P2:P100")
to fill to last rows?

(now wrote P2:P100, when I wrote P:P macro also fill the empty cell)

Code: 
Sub mcrList()
    Range("K:K").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("O1" _
        ), Unique:=True
    Range("P1") = "Date"
    With Range("P2")
        .FormulaArray = "=MAX(IF(K:K=O2,L:L))"
        .AutoFill Destination:=Range("P2:P100")
    End With
    Range("P2:P3").NumberFormat = "dd/mm/yyyy"
End Sub
 
Upvote 0
Hi,

To find the last row in a contiguous range just add this code:

Code: 
Dim r as Range
Dim lastRow as Integer
    Set r = Range("P2")
    lastRow = r.End(xlDown).Row

Forestq said:
Biz, Trevor G,

thanks for help, it's work god.
Now I changed code to select all column with ID and Data. What should I do with

Code: 
.AutoFill Destination:=Range("P2:P100")
to fill to last rows?

(now wrote P2:P100, when I wrote P:P macro also fill the empty cell)

Code: 
Sub mcrList()
    Range("K:K").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("O1" _
        ), Unique:=True
    Range("P1") = "Date"
    With Range("P2")
        .FormulaArray = "=MAX(IF(K:K=O2,L:L))"
        .AutoFill Destination:=Range("P2:P100")
    End With
    Range("P2:P3").NumberFormat = "dd/mm/yyyy"
End Sub
Click to expand...
 
Upvote 0
Code: 
Sub mcrList()
    
    Dim r As Range
    Dim lastRow As Integer
    Set r = Range("P2")
    lastRow = r.End(xlDown).Row     
    
    Range("K:K").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("O1" _
        ), Unique:=True
    Range("P1") = "Date"
    With Range("P2")
        .FormulaArray = "=MAX(IF(K:K=O2,L:L))"
        .AutoFill Destination:=Range("P2:lastRow")
    End With
    Range("P2:lastRow").NumberFormat = "dd/mm/yyyy"
End Sub

But is wrong...
Code: 
.AutoFill Destination:=Range("P2:lastRow")
 
Upvote 0
You need to paste the number into the range string:

Code: 
.AutoFill Destination:=Range("P2:P" + CStr(lastRow))


Forestq said:
Code: 
Sub mcrList()
    
    Dim r As Range
    Dim lastRow As Integer
    Set r = Range("P2")
    lastRow = r.End(xlDown).Row     
    
    Range("K:K").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("O1" _
        ), Unique:=True
    Range("P1") = "Date"
    With Range("P2")
        .FormulaArray = "=MAX(IF(K:K=O2,L:L))"
        .AutoFill Destination:=Range("P2:lastRow")
    End With
    Range("P2:lastRow").NumberFormat = "dd/mm/yyyy"
End Sub
But is wrong...
Code: 
.AutoFill Destination:=Range("P2:lastRow")
Click to expand...
 
Upvote 0
thanks,
works great!

I have other question:
- my "dute date" have a coustom format (dd/mm/yyyy hh:mm) or it's general, but in this macro should be format as "date",
- how to in this code (I think only this line below should be changed) change to date format?

Code: 
.FormulaArray = "=MAX(IF(K:K=O2,[COLOR=red]L:L[/COLOR]))"

PvK
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Solved
Macro to select column to run 'sort by colours' macro on
Replies
3
Views
284
Joe4
Joe4
G
  • Question Question
Sort (Z-A) Macro not working
Replies
1
Views
84
NateSC
NateSC
J
  • Question Question
Rename a Sheet
Replies
2
Views
368
Peter_SSs
Peter_SSs
G
  • Solved
Filter columns (number of rows keeps changing)
Replies
1
Views
331
myall_blues
myall_blues
G
  • Solved
Typing out Excel formula in Excel Macro
Replies
1
Views
70
Macropod
Macropod

Forum statistics

Threads
1,224,505
Messages
6,179,151
Members
452,891
Latest member
JUSTOUTOFMYREACH

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top