Previous Topic Next Topic

Sorting the highest value with dates

Closed
josh07429 Posts 14 Registration date Friday June 23, 2017 Status Member Last seen November 20, 2017 - Jun 23, 2017 at 02:58 PM
 Blocked Profile - Jun 27, 2017 at 05:00 PM
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 responses

Answer 1 / 2
Blocked Profile
Jun 23, 2017 at 04:36 PM
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
josh07429
Jun 23, 2017 at 04:56 PM
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 #)
0
Blocked Profile
Updated on Jun 26, 2017 at 04:29 PM
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)
0
josh07429 Posts 14 Registration date Friday June 23, 2017 Status Member Last seen November 20, 2017 > Blocked Profile
Jun 26, 2017 at 04:52 PM
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
0
Blocked Profile
Jun 26, 2017 at 04:57 PM
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!
0
Answer 2 / 2
Blocked Profile
Jun 27, 2017 at 05:00 PM
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

Similar discussions

encryption message several time in chat
measking -
HelpiOS -

1 response
blank white page for website
yadaves -
ac3mark -

1 response
IF statements with dates and criteria
jimbabama -
Mazzaropi -

3 responses
using web browser page loads but screen blank
inchoate -
leo -

16 responses
Conditional Formatting - highlight overdue dates
TanyaRT -
TanyaRT -

2 responses