Solved Auto-sort range 1, based on range 2 into range 3

March 30, 2013 at 10:14:06
Specs: Windows 7
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.


See More: Auto-sort range 1, based on range 2 into range 3

Report •


✔ Best Answer
March 31, 2013 at 07:28:07
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 position of 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 position of the lookup_value within the specified lookup_array, e.g A1:C1 or D11:D13

OK, 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_value in the lookup_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 Row and 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.



#1
March 30, 2013 at 13:38:22
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.


Report •

#2
March 30, 2013 at 20:08:15
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.

Report •

#3
March 30, 2013 at 20:50:20
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.


Report •

Related Solutions

#4
March 30, 2013 at 23:09:06
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.

Report •

#5
March 31, 2013 at 07:28:07
✔ 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 position of 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 position of the lookup_value within the specified lookup_array, e.g A1:C1 or D11:D13

OK, 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_value in the lookup_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 Row and 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.


Report •

#6
March 31, 2013 at 07:52:26
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_num argument 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_num argument in INDEX is the relative position within 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.


Report •


Ask Question