Order Mix Identifier

[JazzyBeaver]

Hello all,

I have a dataset of orders broken down by the different items and quantities in each order.

Order #	Item Type	SKU	Quantity	Order Mix
1234	Red	103	2	RedGreen
1234	Red	112	7	RedGreen
1234	Green	205	4	RedGreen
1234	Green	217	6	RedGreen
5678	Red	101	9	RedGreenBlue
5678	Green	206	3	RedGreenBlue
5678	Blue	318	2	RedGreenBlue
5678	Blue	301	2	RedGreenBlue
5678	Blue	326	2	RedGreenBlue

<colgroup><col><col><col span="2"><col></colgroup><tbody>
</tbody>

I need a formula to join the different Item Types in each order to create a Order Mix identifier.
I provided an example of what I need in the Order Mix column above.

Idk if it will help find a solution, but Red Items all start with 1, Green items start with 2, and Blue items start with 3.

 

[jtakw]

Hi,

This will achieve what you describe:

Book1
	A	B	C	D	E
1	Order #	Item Type	SKU	Quantity	Order Mix
2	1234	Red	103	2	RedGreen
3	1234	Red	112	7	RedGreen
4	1234	Green	205	4	RedGreen
5	1234	Green	217	6	RedGreen
6	5678	Red	101	9	RedGreenBlue
7	6789	Red	111	1	RedBlue
8	6789	Blue	333	2	RedBlue
9	5678	Green	206	3	RedGreenBlue
10	5678	Blue	318	2	RedGreenBlue
11	5678	Blue	301	2	RedGreenBlue
12	5678	Blue	326	2	RedGreenBlue
Sheet11

Cell Formulas
Range	Formula
E2	=CHOOSE(SUM(IF(COUNTIFS(A$2:A$12,A2,B$2:B$12,"Red"),1,0),IF(COUNTIFS(A$2:A$12,A2,B$2:B$12,"Green"),2,0),IF(COUNTIFS(A$2:A$12,A2,B$2:B$12,"Blue"),4,0)),"Red","Green","RedGreen","Blue","RedBlue","GreenBlue","RedGreenBlue")

Formula copied down.