Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Rick Hess
 
Posts: n/a
Default 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


  #2   Report Post  
Brian V
 
Posts: n/a
Default

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




Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Zero values in a log chart Joelle_Smith Charts and Charting in Excel 2 May 8th 23 03:42 AM
Second serie doesn't use X-as values JackRnl Charts and Charting in Excel 1 January 20th 05 02:04 AM
Missing values in Excel Line Chart mw55309 Charts and Charting in Excel 1 January 19th 05 01:10 PM
linked values in a chart Bill H. Charts and Charting in Excel 2 January 2nd 05 06:29 AM
How to count matching text Duplicateman Excel Discussion (Misc queries) 6 November 26th 04 09:40 AM


All times are GMT +1. The time now is 10:53 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"