Create matrix of combinations from list of values/attributes

[ArsenioV4]

Hi folks!
I’m working on a products dataset I need to conform to a different layout and format and I’m bugged since days with an obstacle I am not able to overcome.

My dataset has SKUs in column A, semicolon-delimited sizes in column B and semicolon-delimited colors in column C.

I would need to create a matrix that outputs on a new sheet all the possible combinations of sizes and colors respectively in columns B and C, along with the corresponding SKU in column A.

This is an example of my starting dataset:

sku	size	color
SKU1	36;37	Black;Red
SKU2	45;46;47	Green;Yellow;Purple
SKU3	40;41;42;43	Blue;Grey;Orange;Teal

And this is my expected output (taking the first 2 SKUs as example):

sku	size	color
SKU1	36​	Black
SKU1	36​	Red
SKU1	37​	Black
SKU1	37​	Red
SKU2	45​	Green
SKU2	45​	Yellow
SKU2	45​	Purple
SKU2	46​	Green
SKU2	46​	Yellow
SKU2	46​	Purple
SKU2	47​	Green
SKU2	47​	Yellow
SKU2	47​	Purple

I would prefer a Formula if possible but I'm open to using PowerQuery as well.
I hope someone is able to help me as I'm really going crazy with this!

Thanks in advance

 

[Oaktree]

Here's one way:

Assuming your SKU/Size/color table is in A1:C4, try:

=VSTACK(
TEXTSPLIT(TEXTJOIN("@",,TOCOL(A2&"|"&TEXTSPLIT(B2,";")&"|"&TRANSPOSE(TEXTSPLIT(C2,";")))),"|","@",TRUE),
TEXTSPLIT(TEXTJOIN("@",,TOCOL(A3&"|"&TEXTSPLIT(B3,";")&"|"&TRANSPOSE(TEXTSPLIT(C3,";")))),"|","@",TRUE),
TEXTSPLIT(TEXTJOIN("@",,TOCOL(A4&"|"&TEXTSPLIT(B4,";")&"|"&TRANSPOSE(TEXTSPLIT(C4,";")))),"|","@",TRUE))

 

[ArsenioV4]

Here's one way:

Assuming your SKU/Size/color table is in A1:C4, try:

=VSTACK(
TEXTSPLIT(TEXTJOIN("@",,TOCOL(A2&"|"&TEXTSPLIT(B2,";")&"|"&TRANSPOSE(TEXTSPLIT(C2,";")))),"|","@",TRUE),
TEXTSPLIT(TEXTJOIN("@",,TOCOL(A3&"|"&TEXTSPLIT(B3,";")&"|"&TRANSPOSE(TEXTSPLIT(C3,";")))),"|","@",TRUE),
TEXTSPLIT(TEXTJOIN("@",,TOCOL(A4&"|"&TEXTSPLIT(B4,";")&"|"&TRANSPOSE(TEXTSPLIT(C4,";")))),"|","@",TRUE))

Thanks Oaktree for your answer. Unfortunately the dataset is quite large with more than 2500 rows (unique SKUs) each with a variable number of options for size and color - would say 1 to 15 for sizes and 1 to 8 for colors.

Sorry for not having specified this before! Do you have any other ideas that don’t involve coding in each cell manually?

Thanks mate!

 

[ArsenioV4]

Problem solved myself using Power Query - not ideal for my case as I would have preferred a formula but at the end it does the job!

I’ll leave the steps here in case anyone else needs this:

1. select range that contains the data you need, including the headers if present;
2. go on Data tab > select From range or table. This will open up Power Query with the selected range from the Excel sheet;
3. select the first column with the list of values/options (in my case it’s Size) by clicking on the column header then go to Transform > Split column > By delimiter (semicolon in my case). This will create “X” columns containing one value each from the list of the original column. In my case the columns will be named “size.1”, “size.2” etc…;
4. do the same for the second (or more) column. You will now have a “X” number of “size” columns and “Y” number of “color” columns in my case;
5. select all the columns for one of the values, “size” in my case, and then right click > Unpivot columns. This will transform the columns giving you one “Attributes” column containing the names of the previous split columns and one “Values” column containing the values for those options, one in each row, and duplicating the other referenced values from the original table rows. Do the same for the other columns for the second set of options (in my case the “color” columns) and at the end you will obtain the data in the needed layout. Click “Save & Load” to load the transformed data in a new Sheet and delete the “Attributes” column(s) if not needed in your data.

You will now have this exact data layout!

sku	size	color
SKU1	36	Black
SKU1	36	Red
SKU1	37	Black
SKU1	37	Red
SKU2	45	Green
SKU2	45	Yellow
SKU2	45	Purple
SKU2	46	Green
SKU2	46	Yellow
SKU2	46	Purple
SKU2	47	Green
SKU2	47	Yellow
SKU2	47	Purple

If someone ever comes across a Formula solution for this, please post it in the thread