How to create constructed variables in Excel

[ezkii]

Hi,

I am trying to create product codes and descriptions for products in our retail store and the items have multiple variables.

I wanted to see if there was a way i could put in the variables (colour, material, size, etc) and have excel create all possible combinations of these for me along with a shortened product code based on the variables.

For example:
Variables = |Blind | White, grey, yellow, blue | 50mm, 60mm| Installed, Not-installed|
code abbreviation = |B |W, G, Y, B | 50, 60 | I, NI|

So I wanted it to generate the code - BW50I (and the description) Blind, White, 50mm, Installed - for all possible variables (e.g. BG50I, BY50I, BB50I, BW60I, ETC ETC ETC & DESCRIPTIONS)

I may have not explained it well sorry. I know this is probably way outside the boundaries of what Excel is capable of but just thought i'd ask before i create 5000 product codes manually.

Any help is appreciated.

Thank you

[FDibbins]

Hi, welcome to the forum

Please take a moment to read the forum rules and then amend your thread title to something descriptive of your problem - not what you think the answer might be. (think google search terms?). Once you have done this please send me a PM and I will remove this request. (Also, include a link to your thread - copy from the address bar)

Many members search our previous posts, and thread titles play a big part of the search. I doubt anybody would do a search based on your title?

To change a Title on your post, click EDIT POST then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

(note: this change is not optional )

[FlameRetired]

Hi ezkii. Welcome to the forum.

Since it appears you've made appropriate title change the following formula does this.

It uses array constants that could just as easily be replaced with lookup tables. Here's the brute force version.

Please Login or Register  to view this content.

Or with lookup ranges.

Please Login or Register  to view this content.

	A	B
1	BW50I	Blind, White, 50mm, Installed
2	BG60NI	Blind, grey, 60mm, Not-installed
3	BY50I	Blind, yellow, 50mm, Installed
4	BB60I	Blind, blue, 60mm, Installed

	E	F	G	H	I	J	K	L
9	50	60	BB	BG	BW	BY	I	NI
10	, 50mm	, 60mm	Blind, blue	Blind, grey	Blind, White	Blind, yellow	, Installed	, Not-installed