Combination, vlookup+indexing : unsorted lists

furian76

New Member
Joined
Nov 16, 2009
Messages
7
Hello all, my first time posting to this forum. I have watched a lot of the youtube videos and found a lot of good information in there. Specially for cleaning up my code later on. I appreciate your time, if your able to point me in the right direction. I may be asking the wrong question in regards to which function I wish to use, but I am sure someone may be able to push me in the right direction.

I will try to describe my sheet first, then pose the question related to it.

10+ columns with static data. Some of the data is not there yet.

Excel%20data.JPG

LF-fAU2TSk21wzK3MFiEPw

This image show some of the data, But basically, you see how I had to create an origin and receiving catagory, then also a combination of the 2. Then from there individually assign a cost to each one. Is there a way to pair the to and from location, and assign a cost based on the pair, so that duplication of the data is not needed.

I hope that I articulated the data correctly, but not sure. I can see the final product of these formulas in my head, and I could fake it using power point to resemble the final product, but doing the formulas, I am lacking in this department. My most complex formula I am currently using is
Code:
=VLOOKUP(B2,Chart,3,FALSE)
And this works well, But I wish to use these newly derived cell responses to drive additional data into other cells.

I suck at articulatiing issues in text. Just looking for a push in the right direction. I don't mind looking up the answer, as long as I know what I am looking for. Thanks for any assistance.
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

furian76

New Member
Joined
Nov 16, 2009
Messages
7
I was looking for a way to edit my first post, but I don't see it anywhere.

I was going to add specs:

Excel 2007, Windows XP Pro. Beginner level user.<-- at least that's how I feel.:(

Now see that is weird. I can edit my second post but not the original. Anyways, thanks for any leads or tips.
 

Jobowo

Board Regular
Joined
Oct 21, 2009
Messages
242
Not easy to see what you are trying to do without knowing what is in B2. Was this supposed to be W2? Your costs are determined by a combination of Origin/Receiving? Are these combinations in "Chart"?

It's a long shot but do you just need to concatenate Origin & Receiving for the lookup?

VLOOKUP(U2 & ":" & V2,chart,false)
 

gecs

Active Member
Joined
Jan 26, 2009
Messages
320
Hello all, my first time posting to this forum. I have watched a lot of the youtube videos and found a lot of good information in there. Specially for cleaning up my code later on. I appreciate your time, if your able to point me in the right direction. I may be asking the wrong question in regards to which function I wish to use, but I am sure someone may be able to push me in the right direction.

I will try to describe my sheet first, then pose the question related to it.

10+ columns with static data. Some of the data is not there yet.

Excel%20data.JPG

LF-fAU2TSk21wzK3MFiEPw

This image show some of the data, But basically, you see how I had to create an origin and receiving catagory, then also a combination of the 2. Then from there individually assign a cost to each one. Is there a way to pair the to and from location, and assign a cost based on the pair, so that duplication of the data is not needed.

I hope that I articulated the data correctly, but not sure. I can see the final product of these formulas in my head, and I could fake it using power point to resemble the final product, but doing the formulas, I am lacking in this department. My most complex formula I am currently using is
Code:
=VLOOKUP(B2,Chart,3,FALSE)
And this works well, But I wish to use these newly derived cell responses to drive additional data into other cells.

I suck at articulatiing issues in text. Just looking for a push in the right direction. I don't mind looking up the answer, as long as I know what I am looking for. Thanks for any assistance.

A way of pairing the "from" and "to" destinations can be like in the following example. In column A is the list of cities, transposed then in the first row starting with column B - in the resulting table just type in the values, considering the list in column A the "from's" and the list in row 1 the "to's". Then, select the list in column A (A2:A6) and name it "_from", select the list in row 1 (B1:F1) and name it "_to", select the numbers (B2:F6) and name them "pallet_costs" (all the names should be written without quotes), then to retrieve the costs use the formula in C9, copied down as many times you need. To avoid unnecessary typing you can also use Data Validation of type "list" for the cells below the "From" and "To" labels.

Excel Workbook
ABCDEF
1*Mentor - 4005Menasha - 4001Urbana - 4003Georgetown - 4045Bardstown - 4046
2Mentor - 4005$0.00$1.00$2.00$3.00$4.00
3Menasha - 4001$1.00$0.00$3.00$4.00$6.00
4Urbana - 4003$2.00$3.00$0.00$9.00$10.00
5Georgetown - 4045$3.00$4.00$9.00$0.00$2.00
6Bardstown - 4046$4.00$6.00$10.00$2.00$0.00
7******
8FromToCosts***
9Mentor - 4005Georgetown - 4045$3.00***
10Urbana - 4003Menasha - 4001$3.00***
11Georgetown - 4045Menasha - 4001$4.00***
12Bardstown - 4046Menasha - 4001$6.00***
13Urbana - 4003Bardstown - 4046$10.00***
Sheet1
#VALUE!
</td></tr></table></td></tr></table>
 

furian76

New Member
Joined
Nov 16, 2009
Messages
7

ADVERTISEMENT

I love the words used. I did figure out that I assume that means combine or link. But I realize that combine is similar to sum. Anyways, sort of.

B2 is correct, I didn't to put so many pictures in here that it would flood everyone out. But I will take an over all one shot. Hopefully the image is not to over whelming.

I have B2 and B7 as drop down lists pulled from the charts.

W2 and down is actually:
Code:
=$U$2&" : "&V2
with changes as it goes down.

yellow indicates drop down list, green indicates Auto filled by formula, blue means you must enter information.

Using the the B2 drop down it auto fills all of the greens. This would include B7 if I can figure it out. Specific base bins come from a dedicated location.

I would add a check box to indicate fabrication and another to indicate random (or something else). If Fabrication is selected the receiving plant will always be Georgetown, and the drop down would auto select the origin plant. For random (or other) 1 drop down lists become available in B9 for selection of a receiving plant and the origin plant would already be defined by B2.

With that said, there is a column not currently made that will assign a foot print to each Base Bin. Which will include data like stack height, layout and max fit. Same goes for the walls. And this list would have data for max pallets per truck, and also do a calculation for if I had a partial truck and figure out the density for multiple foot prints. From there it will do the math based on E11, multiplied by C5, F5, I5, and L5 with a >0 call out type of thing so that if a "0" is present it does not use it.

Then it will use the pricing populated into E7 for full truck load costs and E9 for Pallets. So that partial loads can be calculated. Then it will show final information in the orange area. Where toal will read : 5TL / 10PL based on the information supplied. Then the Cost: would be pallets*E9+Truck loads*E7. What this does at the end of the day, is reduces calculation time from 30 minutes to an hour per project to about 5 minutes. And I would only have to update pricing 2-3 times per year. All other information generally stays static. But would be easily configurable down the line.

Attached is the full image. Thanks for the input.
Excel%20Overvue.JPG
 

furian76

New Member
Joined
Nov 16, 2009
Messages
7
A way of pairing the "from" and "to" destinations can be like in the following example. In column A is the list of cities, transposed then in the first row starting with column B - in the resulting table just type in the values, considering the list in column A the "from's" and the list in row 1 the "to's". Then, select the list in column A (A2:A6) and name it "_from", select the list in row 1 (B1:F1) and name it "_to", select the numbers (B2:F6) and name them "pallet_costs" (all the names should be written without quotes), then to retrieve the costs use the formula in C9, copied down as many times you need. To avoid unnecessary typing you can also use Data Validation of type "list" for the cells below the "From" and "To" labels.

Excel Workbook
ABCDEF
1*Mentor - 4005Menasha - 4001Urbana - 4003Georgetown - 4045Bardstown - 4046
2Mentor - 4005$0.00$1.00$2.00$3.00$4.00
3Menasha - 4001$1.00$0.00$3.00$4.00$6.00
4Urbana - 4003$2.00$3.00$0.00$9.00$10.00
5Georgetown - 4045$3.00$4.00$9.00$0.00$2.00
6Bardstown - 4046$4.00$6.00$10.00$2.00$0.00
7******
8FromToCosts***
9Mentor - 4005Georgetown - 4045$3.00***
10Urbana - 4003Menasha - 4001$3.00***
11Georgetown - 4045Menasha - 4001$4.00***
12Bardstown - 4046Menasha - 4001$6.00***
13Urbana - 4003Bardstown - 4046$10.00***
Sheet1
#VALUE!
</td></tr></table></td></tr></table>

Oooh, I like this. Never saw the LEN call out before. I will have to look that up. After looking at your layout. I can see mine is horribly placed. I will have to re manage some of my layouts so that the reflect an easier to read layout. Thanks alot.
 

furian76

New Member
Joined
Nov 16, 2009
Messages
7
GECS, thanks for posting that LEN's thing.I was able to mutilate it some and get it working based on what you did there. For the most part anyways. i wasn't sure what each statement was saying at first, but I think I get it now. Instead of filling in static areas from A9 to B13, I made a drop down list for from and to. Then amended to formula. One that uses pallet_costs and the other using truckload_costs. Both pull there from and to locations from the same drop down lists. This was extremely helpful. I have also reorganized my lists for better functionality bases on what you showed. I am sure I will have more questions later concerning other aspects but I am hoping this will be enough to kick me in the right direction. thanks again for all that responded.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,816
Messages
5,638,496
Members
417,029
Latest member
lingx86

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