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