Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Zero values in a log chart | Charts and Charting in Excel | |||
Second serie doesn't use X-as values | Charts and Charting in Excel | |||
Missing values in Excel Line Chart | Charts and Charting in Excel | |||
linked values in a chart | Charts and Charting in Excel | |||
How to count matching text | Excel Discussion (Misc queries) |