Need Data converted using specific set criteria

[Karein]

I'm a bit of a novice Excel formula user/creator. I have an extensive formula to create and I'm not sure if it's even possible.

I have 24 different products (see below).

		Sprinkles		Sprinkles
	Vanilla	Vanilla	Chocolate	Chocolate
Plain	x	x	x	x
Powdered	x	x	x	x
Old_Fashioned	x	x	x	x
Coconut	x	x	x	x
Jelly	x	x	x	x
Cinnamon	x	x	x	x

<tbody>
</tbody>

The way the information comes to me is via an Excel spreadsheet (see below) - it's set and can't be changed.
Some of the products come in with a different name (Straight-Forward=Plain / Grape Jelly=Jelly / Dust=Powdered)

Vanilla_Flavor	Vanilla_Flavor_Qty
Straight_Forward	1
Dust	1
Straight_Forward	1
Cinnamon	3

<tbody>
</tbody>

I need to have the formula read the Variety and Flavor to produce the correct Name. The complication comes when there are multiple products (see example below):

The order will come in like this:

	Vanilla_Flavor	Vanilla_Flavor_Qty	Vanilla_Flavor_w_Sprinkles	Vanilla_Flavor_w_Sprinkles_Qty	Chocolate_Flavor	Chocolate_Flavor_Qty	Chocolate_Flavor_w_Sprinkles	Chocolate_Flavor_w_Sprinkles_Qty
Customer#1					Straight_Forward	1
Customer#2
Customer#3							Cinnamon	1
Customer#4			Cinnamon	1	Cinnamon	1
Customer#5	Straight_Forward	1			Straight_Forward	1
Customer#6			Cinnamon	3
Customer#7	Cinnamon	1			Cinnamon	1
Customer#8					Cinnamon	1
Customer#9	Connamon	1			Cinnamon	1
Customer#10					Cinnamon	1

<tbody>
</tbody>

I need the formula to give me this:

	Product	Qty	Product	Qty
Customer#1	Plain_Chocolate_Donut	1
Customer#2
Customer#3	Cinnamon_Chocolate_w_Sprinkles_Donut	1
Customer#4	Cinnamon_Vanilla_w_Sprinkles_Donut	1	Cinnamon_Chocolate_Donut	1
Customer#5	Plain_Vanilla_Donut	1	Plain_Chocolate_Donut	1
Customer#6	Cinnamon_Vanilla_w_Sprinkles_Donut	3
Customer#7	Cinnamon_Vanilla_Donut	1	Cinnamon_Chocolate_Donut	1
Customer#8	Cinnamon_Chocolate_Donut	1
Customer#9	Cinnamon_Vanilla_Donut	1	Cinnamon_Chocolate_Donut	1
Customer#10	Plain_Chocolate_Donut	1

<tbody>
</tbody>

THANKS IN ADVANCE!!

 

[cerfani]

Not sure I follow this convoluted method correctly so all I can say is I recommend you create a SKU Catalog and list your 24 SKUs in a table, include their alias and any other relevant info that you would want to be able to query and retrieve.

I think if you could reorganize your data into tables, it would make calculations much easier. Whenever you work with Excel the preferred format for data is to be in a table. Header on top and data below. What you need sounds possible but I think you are creating extra work for yourself.

Make a table with the aliases at least and when you read, for example, "dust," you will have to lookup dust in a table and see what it means.

I would write a formula but I cant follow all this. Id probably have to sit down with you or something and see your files to suggest something.

 

[Karein]

Okay... yea, I'm SURE I'm making more work than needs be!

Thank You for your response! I'll try creating a table and see if I can make it work that way!