Looking Up the intersection point in a table that matches multiple criteria

oneal1767

New Member
Joined
Jul 25, 2014
Messages
1
I have a list of cities in column A with a set of values assigned to them in column B. Is there a formula I can use to determine which cities values would go together with the sum being between two numbers and without including more than three cities. For example if the range that the sum must be between is 15,000 to 16,000, I need to know all the possible scenarios of pairing the cities up where the sum of the values assigned to them will equal this range. Also keep in mind this could be the sum of up to 3 of the numbers. Then I have a list of the same cities in a table showing the distance between each city. Is there a way to take the scenarios given from above and analyze them so it will identify the shortest distance to travel? I've pasted a section of this table below. What I'm trying to do is to create a file that will help me to plan deliveries. The table below will show all of the cities that we deliver to. The list of cities will have the weight of material I have to deliver in each city. I'd like to determine which cities could be paired together without going over three stops where the weight would be within the range listed above, and then determine the best route. For example City A may be able to be matched with either city B or city C, but the distance between city A and B is less than between city A and C. Therefore I would want to match A and B together.
Row LabelsAGRICOLA, MSARABI, LAATMORE, ALBAKER, FLBATON ROUGE, LABAY MINETTE, ALBAY SPRINGS, MSBAY ST LOUIS, MS
AGRICOLA, MS0136761291926010784
ARABI, LA13601922368517616358
ATMORE, AL7619205524820169140
BAKER, FL13123655029275216184
BATON ROUGE, LA192842482920232207114
BAY MINETTE, AL6017620752320152124
BAY SPRINGS, MS1071631682162061520136
BAY ST LOUIS, MS84591401841141241360

<colgroup><col width="142" style="width: 106pt; mso-width-source: userset; mso-width-alt: 5034;"> <col width="114" style="width: 85pt; mso-width-source: userset; mso-width-alt: 4039;"> <col width="102" style="width: 77pt; mso-width-source: userset; mso-width-alt: 3640;"> <col width="99" style="width: 74pt; mso-width-source: userset; mso-width-alt: 3527;"> <col width="93" style="width: 70pt; mso-width-source: userset; mso-width-alt: 3299;"> <col width="136" style="width: 102pt; mso-width-source: userset; mso-width-alt: 4835;"> <col width="129" style="width: 97pt; mso-width-source: userset; mso-width-alt: 4579;"> <col width="131" style="width: 98pt; mso-width-source: userset; mso-width-alt: 4664;"> <col width="133" style="width: 100pt; mso-width-source: userset; mso-width-alt: 4721;"> <tbody>
</tbody>
 

Some videos you may like

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

Watch MrExcel Video

Forum statistics

Threads
1,122,437
Messages
5,596,112
Members
414,042
Latest member
Swiftkoala

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top