Auto-sort line 2 into line 4 based on line 3 - longest to sh

I have 3 ranges (rows) of data (times). The data is input into row 2, based on the order of row 1 as follows: 1 - Departure location (set)

2 - Travel time (input)

3 - Departure order (longest to shortest) (auto-sorted)I want the row 1 data to be auto-sorted into row 3 based on longest duration to shortest duration in row 2. I think this one has pushed me to the limit of my knowledge and I've been trying to find the answer from dozens of sites, but ... you are my last hope.

✔ Best Answer

Per the Excel help files: MATCH(

lookup_value, lookup_array, [match_type])"The MATCH function searches for a specified item in a range of cells, and then returns the

relative positionof that item in the range."e.g.

A B C 1 21 33 42=MATCH(33,A1:C1,0) would return 2 because 33 is in the 2nd position of the range.

e.g.

D 11 21 12 33 13 42=MATCH(33,D11:D13,0) would also return 2 because 33 is in the 2nd position of the range.

MATCH doesn't care about the actual Excel Column or Row because it returns the

relative positionof thelookup_valuewithin the specifiedlookup_array, e.g A1:C1 or D11:D13OK, so let's look at the formula I suggested, and break it down.

=MATCH(

lookup_value, lookup_array, [match_type])=MATCH(LARGE($B$3:$I$3,COLUMN()-1),$B$3:$I$3,0)

lookup_value: LARGE($B$3:$I$3,COLUMN()-1)The COLUMN() function returns the Column number in which it resides. e.g. In Column B, COLUMN() will return 2. In Column C, =COLUMNS() will return 3, etc.

Therefore in Column B, LARGE($B$3:$I$3,COLUMN()-1) will be equivalent to:

=LARGE($B$3:$I$3,1) which will return the longest Travel Time in B3:I3. As you drag that across, the value that COLUMNS() will return will increment by 1, giving you:

=LARGE($B$3:$I$3,2) in Column C

=LARGE($B$3:$I$3,3) In Column D

etc.LARGE returns the actual Travel Time from B3:I3, but we want the Departure Location, right? To solve that we use MATCH.

Since MATCH returns the relative position of the

lookup_valuein thelookup_array, the entire formula returns the relative position of the LARGEest (longest) Travel Time first, then the next LARGEest (next longest), etc.You'll note that it is not returning the actual number from Row 2 (your departure locations) instead it is returning the relative position of the values in B3:I3 which just happen to match your Departure Locations.

Now, the problem with MATCH is that it returns the relative position of the first match it finds. Therefore, if you have the same value in e.g. relative position 2 and relative position 5, MATCH will return 2 for both of them.

The way to fix that is to alter the values that you are trying to match so that there will never be a 2 or more values equal to each other. That can be done with what is referred as a Helper Row (or Column).

For ease of explanation, I'll suggest a Helper Row.

Insert a Row above your Travel Times. In the new B3 enter this formula and drag it across to I3:

=B4-COLUMN()/10000000

What this will do is decrease your Travel Times by a tiny amount by using the COLUMN() function so that no Travel Times will be equal to each other. If the denominator is large enough, you won't even see the difference but internal to Excel all of the numbers will be different.

Now, use the MATCH formula on the

Helper Rowand you'll get unique relative positions (or Departure Locations) for each Travel Time.=MATCH(LARGE($B$3:$I$3,COLUMN()-1),$B$3:$I$3,0)

You can hide the Helper Column if you don't want to see it.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

I can break this down for you if you need me to, but it might be fun for you to try and figure out how it works: =MATCH(LARGE($B$3:$I$3,COLUMN()-1),$B$3:$I$3,0)

This works for the values in your example, however, if there will be a situation where 2 or more travel times will be equal, you will not get accurate results. Will that be a problem?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Thanks, I'm going to play with this as MATCH is new to me, but yes, there might be more than one situation with 2 or more of the same travel times.

Well, what do you want to happen as far as the sorting goes if 2 or more travel times are the same? If e.g. Locations 4 and 6 have the same Travel Time, how do you want them to appear in the sorted list?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

One after the other would be best if that is possible. I'm stuggling with the MATCH, so very much appreciate your help with this DerbyD.

Per the Excel help files: MATCH(

lookup_value, lookup_array, [match_type])"The MATCH function searches for a specified item in a range of cells, and then returns the

relative positionof that item in the range."e.g.

A B C 1 21 33 42=MATCH(33,A1:C1,0) would return 2 because 33 is in the 2nd position of the range.

e.g.

D 11 21 12 33 13 42=MATCH(33,D11:D13,0) would also return 2 because 33 is in the 2nd position of the range.

MATCH doesn't care about the actual Excel Column or Row because it returns the

relative positionof thelookup_valuewithin the specifiedlookup_array, e.g A1:C1 or D11:D13OK, so let's look at the formula I suggested, and break it down.

=MATCH(

lookup_value, lookup_array, [match_type])=MATCH(LARGE($B$3:$I$3,COLUMN()-1),$B$3:$I$3,0)

lookup_value: LARGE($B$3:$I$3,COLUMN()-1)The COLUMN() function returns the Column number in which it resides. e.g. In Column B, COLUMN() will return 2. In Column C, =COLUMNS() will return 3, etc.

Therefore in Column B, LARGE($B$3:$I$3,COLUMN()-1) will be equivalent to:

=LARGE($B$3:$I$3,1) which will return the longest Travel Time in B3:I3. As you drag that across, the value that COLUMNS() will return will increment by 1, giving you:

=LARGE($B$3:$I$3,2) in Column C

=LARGE($B$3:$I$3,3) In Column D

etc.LARGE returns the actual Travel Time from B3:I3, but we want the Departure Location, right? To solve that we use MATCH.

Since MATCH returns the relative position of the

lookup_valuein thelookup_array, the entire formula returns the relative position of the LARGEest (longest) Travel Time first, then the next LARGEest (next longest), etc.You'll note that it is not returning the actual number from Row 2 (your departure locations) instead it is returning the relative position of the values in B3:I3 which just happen to match your Departure Locations.

Now, the problem with MATCH is that it returns the relative position of the first match it finds. Therefore, if you have the same value in e.g. relative position 2 and relative position 5, MATCH will return 2 for both of them.

The way to fix that is to alter the values that you are trying to match so that there will never be a 2 or more values equal to each other. That can be done with what is referred as a Helper Row (or Column).

For ease of explanation, I'll suggest a Helper Row.

Insert a Row above your Travel Times. In the new B3 enter this formula and drag it across to I3:

=B4-COLUMN()/10000000

What this will do is decrease your Travel Times by a tiny amount by using the COLUMN() function so that no Travel Times will be equal to each other. If the denominator is large enough, you won't even see the difference but internal to Excel all of the numbers will be different.

Now, use the MATCH formula on the

Helper Rowand you'll get unique relative positions (or Departure Locations) for each Travel Time.=MATCH(LARGE($B$3:$I$3,COLUMN()-1),$B$3:$I$3,0)

You can hide the Helper Column if you don't want to see it.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

BTW...As I mentioned in my previous post, using MATCH does not actually return the Departure Location, but it works for the example you posted because you used numbers for your Departure Locations which are the same as the values that the MATCH function will return. In other words, MATCH returns 1, 2, 3, etc. and your Departure Locations just happen to be 1, 2, 3, etc. However, if your Departure Locations are not just numbers, e.g. Gate 1, Gate 2, etc. then you need to wrap the MATCH formula with an INDEX function which will allow you to return the actual Departure Location from Row 2.

The syntax of the INDEX function is:

INDEX(

array, row_num, [column_num])=INDEX($B$2:$I$2,1,MATCH(LARGE($B$3:$I$3,COLUMN()-1),$B$3:$I$3,0))

With your Departure Locations in B2:I2, and your "altered" Travel Times in B3:I3, this formula will use the MATCH function to determine the

column_numargument for the INDEX function and return the value in that position from Row 1 of the Array $B$2:$I$2.Similar to MATCH, the

column_numargument in INDEX is therelative positionwithin the array, not an actual Excel Column number.In other words, Column 4 in Excel is Column D. Column 4 in the range F1:K1 is Column I because Column F is the first column in that range.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Ask Your Question

Weekly Poll

Do you think Jony Ive could make a big impact on Airbnb?

Discuss in The Lounge

Poll History