Sorting the highest value with dates

Closed
Report
Posts
16
Registration date
Friday June 23, 2017
Status
Member
Last seen
November 20, 2017
-
 Blocked Profile -
Need help with formula or script
Where I need to look for the Van's highest Passenger# with the latest month available


for example
van 800's latest month is 6(JUNE) and his highest passenger # is 13, I need to get 13 and delete the other van 800
van 805's latest month is 5(MAY) and his highest passenger # is 14, I need to get 14 and delete the other van 805

I need a formula or script that will look for the vans latest month and highest passenger #

Thanks in advance

2 replies


No, you need to just sort the list. Highlight the columns you would like to sort in order, and select SORT. No programming needed!

But if you must, take a look at this:


Range("A6:AZ9999").Sort key1:=Range("AW5"), _
order1:=xlDescending, Header:=xlNo


The key1 entry is the column you wish to sort by!
1
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2821 users have said thank you to us this month

Hi thanks for the reply..

I am trying to create a macro tool that will sort the list to get the A(Van#) with the latest month(column B) that has the highest value(passenger #) then eliminate the duplicates with low passenger number and old month

If you could help me with the macro script that would be great.

this is just 1 process from the tool i am creating the resukt from this worksheet will be reference to my vlookup to get the correct columnC (highest passenger #)
Sure I will help. Post some code so that I can see where it is failing! I do not provide turn key solutions or scripts, but assist when stuck on your own code.

Have you looked into the MAX function? SOmething like:

=MAX(A1:A16)
Posts
16
Registration date
Friday June 23, 2017
Status
Member
Last seen
November 20, 2017
> Blocked Profile
yes I have tried =MAX but I'm figuring out how to use max twice in one formula
I tried to change to date by using =month so that i can get the numbers of the month instead of the whole date then I tried using MAX twice like get van#300 and its max in column B then get its max in column C. but its a little trickier that i thought lol
Blocked Profile
Do a loop like in your last code. ONce you have the logic test, just about anything can be scaled to fit into the code in your last post. It just may not be as pretty, and may take more than 1 line!

Hang in there!

You can try:

=vlookup(max(A1:a30),A1:c30,3,False) & "-" & vlookup(max(A1:a30),A1:c30,2,False)


That would produce two seperate columns from the highest value in the column.

1
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2821 users have said thank you to us this month