Matching a List Containing Redundant Values
 

I have a set of numbers (auto mileage), located in a horizontal range named
Rental_Totals.
I have a corresponding set of addresses, located in a horizontal range named
Rental_Addresses, matched to Rental_Totals.

I need the two ranges automatically sorted vertically in ascending order of
Rental_Totals. To do this, I located a post from Bob Phillips (
http://tinyurl.com/6k462 ) that almost* solves my problem, and I handled it
like this:

Under heading Auto Mileage, I drag this down a column starting at F4:
= SMALL( Rental_Totals, ROW(A1) )

Under heading Rental Addresses, I drag this down a column starting at C4:
= INDEX( Rental_Addresses, 0, MATCH(F4, Rental_Totals, 0) )

*The problem is that there can be more than one address with the same
mileage value; when that happens, the same address (first occurrence)
repeats for that mileage. I can't figure out how to MATCH a second or
subsequent occurrence of duplicate mileage values.

The only scheme I have thought of is to reference the cells (instead of the
named range) and, when a duplicate mileage value is found, MATCH on the
remaining cells to the right. This seems overly complicated, especially
since there could be dozens of duplicate mileage values.

I have no working knowledge of VBA, which is why Mr. Phillips's solution
appealed to me. Is there a non-VBA solution?

--


Rick Hess
New Orleans
To reply, eliminate All_Spammers

Rick,
You can use an array formula. See Chip Pearson's samples on arbitrary
lookups: http://www.cpearson.com/excel/lookups.htm

-BV

"Rick Hess" wrote in message
...
I have a set of numbers (auto mileage), located in a horizontal range
named
Rental_Totals.
I have a corresponding set of addresses, located in a horizontal range
named
Rental_Addresses, matched to Rental_Totals.

I need the two ranges automatically sorted vertically in ascending order
of
Rental_Totals. To do this, I located a post from Bob Phillips (
http://tinyurl.com/6k462 ) that almost* solves my problem, and I handled
it
like this:

Under heading Auto Mileage, I drag this down a column starting at F4:
= SMALL( Rental_Totals, ROW(A1) )

Under heading Rental Addresses, I drag this down a column starting at C4:
= INDEX( Rental_Addresses, 0, MATCH(F4, Rental_Totals, 0) )

*The problem is that there can be more than one address with the same
mileage value; when that happens, the same address (first occurrence)
repeats for that mileage. I can't figure out how to MATCH a second or
subsequent occurrence of duplicate mileage values.

The only scheme I have thought of is to reference the cells (instead of
the
named range) and, when a duplicate mileage value is found, MATCH on the
remaining cells to the right. This seems overly complicated, especially
since there could be dozens of duplicate mileage values.

I have no working knowledge of VBA, which is why Mr. Phillips's solution
appealed to me. Is there a non-VBA solution?

--


Rick Hess
New Orleans
To reply, eliminate All_Spammers