Assigning a value

[zloep]

How do I go about assigning a number to a particular piece of data? To give a simple example of what I'm trying to do, and what I envision, I'll pretend I'm building a sandwich.

1=white
2=wheat
3=ham
4=turkey
5=roast beef
6=lettuce
7=tomato
8=mayo
9=mustard
10=cheese

So, someone could come along and build their sandwich, ham/turkey/tomato/mayo on white bread. Excel would then recognize that 1+3+4+7+8=23
23=The American (the name of the sandwich, which I've already assigned a variable to)

I'm using the sandwich model because its a lot simpler than what I'm attempting to do.

[Ron Coderre]

To make sure this one doesn't turn into an "oh, one more thing" nightmare....
Try posting what you really want to do, with some data a samples and the results you want to achieve.

[zloep]

I just need to know the easiest/most effective way to assigning a numerical value to a piece of data.

I build geothermal units and there are about 500 different configuarations a customer can choose from. I've created a set of 10 unique questions that, when answered, a customer will know the exact model number of the unit he's seeking. Most questions have two possible answers (Commercial or residential, Vertical or Horizontal, Right return or Left return, Packaged or Split-System). I've created dependent dropdown lists to guide the customers through the options, as some options open up different options. If I assign a value to each option, and then a value to each model number that corresponds to the sum of a set of options, once they complete the 10 questions, they'll know their exact model number.

[shg]

So, someone could come along and build their sandwich, ham/turkey/tomato/mayo on white bread. Excel would then recognize that 1+3+4+7+8=23=The American

Couldn't that also be roast beef, cheese, and lettuce on wheat?

[e4excel]

Zloep,

From your Example are you trying to get a double digit number which is assigned a value..?
Then you can use VLOOKUP to good effect..

1=white
2=wheat
3=ham
4=turkey
5=roast beef
6=lettuce
7=tomato
8=mayo
9=mustard
10=cheese

Let's say you have the Values on the right of the Items
Like White in Col A and 1 in Column B

Just for explanation I select 3 items,then

Lets say Wheat + Turkey + Mayo

=SUM(VLOOKUP("Wheat",$A:$B,2,FALSE),VLOOKUP("Turkey",$A:$B,2,FALSE),VLOOKUP("Mayo",$A:$B,2,FALSE)))

[Ron Coderre]

I attached a sample workbook that calculates a model number
from the user's selections of dropdown lists.

I'd describe it, but the workbook does a better job of presenting the info.

Is that something you can work with?

[shg]

Good job, Ron, the thread made me hungry and I had to go eat ...

[Ron Coderre]

Good job, Ron

Thanks, Shg...Now let's see if that approach applies to zloep's situation.

[e4excel]

Great File Ron...
Indeed it's a great file crisp and concise coding....

Just need to wait and watch the owners Zloep's take on it..

[ =SUM(IF(TRANSPOSE(C3:C7)<>"",(TRANSPOSE(C3:C7)=G3:K6)*(ROW(G3:G6)-2)*10^{4,3,2,1,0},)) ]

Can u please explain what magic have u done?

I feel so small when I look at my VLookup appraoch..
when I look at the code from Stalwarts like you...

[Ron Coderre]

Thanks for the kind words.

Please Login or Register  to view this content.

Essentially, that formula calculates the dropdown list position
of each item in the user selections and builds a
5-digit "smart field", where each digit has meaning.
In this code: 12123
Postion 1: TYPE
Postion 2: ALIGNMENT
Postion 3: ROTATION
Postion 4: PKGTYPE
Postion 5: CONFIGURATION

The formula determines which row in the dropdown source list range
contains the selected value, then multiplies it by a power of 10
to position it in the code.

Example:

Please Login or Register  to view this content.

Because user options are in a vertical array, the TRANSPOSE functions
are necessary to convert them to a horizontal array.

I hope that helps.

[e4excel]

Thanks for the kind words.

I hope that helps.

You deserved them...
Thank you so much for the explanation...

Long way to go for me and so many others..

Im just dying to use this logic somewhere...

Im learning everyday and am proud to be a Student...with great and brilliant people like you...

Zloep..You carved an amazing query and Ron complemented it with such a great solution..

Happy Ending!