Data re-arrangement issue

[yuvalshabt]

I have to following clothing related data.

Its a sheet of the item's ID, sizes and the quantity.

Different items have different size chart (like shoes are 10,11,12 but shirts are S M L...)
so in this table we have "tag" that we can visually match the correct size to the quantity.

I need to re-arrange that data so it will show:

item's id"-"size in one cell

and quantity in another cell.

For example - sku1 - marked as tag #4 so the first quantity is 0 for size 30, then 1 quantity for size 30.5....

sku3 is "tag" #3 and has 6 quantity size M.


Any help will be much appreciated!!! thank you all!

Please see pic for reference:

 

[SanjayGMusafir]

Share sample data using XL2BB and also update your profile with Current version of Excel and platform to give you an optimal solution. Links below

 

[yuvalshabt]

New Microsoft Excel Worksheet.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1			1	XS		S		M	L	XL	XXL
2			2	OS
3			3	36		38		40	42	44	46
4			4	30		30.5		31	31.5	32	32.5
5			tag	quantity		quantity		quantity	quantity	quantity	quantity
6	SKU1		4	0		1		5	1	3	2
7	SKU2		2	1
8	SKU3		3	4		0		6	0	0	0
9	SKU4		1	0		5		6	0	0	0
10
11	wanted output:
12			A1+"-"+tag identifier					quantity
13			SKU1-30					0
14			SKU1-30.5					1
15			SKU1-31					5
16			SKU1-31.5					1
17			SKU1-32					3
18			SKU1-32.5					2
19
Sheet3

 

[SanjayGMusafir]

Check this and revert -

Book1
	A	B	C	D	E	F	G	H	I
1			1	XS	S	M	L	XL	XXL
2			2	OS
3			3	36	38	40	42	44	46
4			4	30	30.5	31	31.5	32	32.5
5			tag	quantity	quantity	quantity	quantity	quantity	quantity
6	SKU1		4	0	1	5	1	3	2
7	SKU2		2	1
8	SKU3		3	4	0	6	0	0	0
9	SKU4		1	0	5	6	0	0	0
10
11	wanted output:		Tag	4
12			A1+"-"+tag identifier			quantity
13			SKU1-30	SKU1-30	0	0
14			SKU1-30.5	SKU1-30.5	1	1
15			SKU1-31	SKU1-31	5	5
16			SKU1-31.5	SKU1-31.5	1	1
17			SKU1-32	SKU1-32	3	3
18			SKU1-32.5	SKU1-32.5	2	2
Sheet1

Cell Formulas
Range	Formula
D13:D18	D13	=LET(SKU,XLOOKUP($D$11,$C$6:$C$9,$A$6:$A$9),Sz,FILTER($D$1:$I$4,$C$1:$C$4=$D$11),TRANSPOSE(SKU&"-"&Sz))
E13:E18	E13	=TRANSPOSE(FILTER($D$6:$I$9,$C$6:$C$9=$D$11))
Dynamic array formulas.

 

[yuvalshabt]

Thank you!
The final result that I need is basically have sku, quantity and relavant size is a more readable, eay-to-work way...
I need to run this for all the skus and their different sizes...
Is there any way to "recreate" the original table and add the quantity to the size as follows:

New Microsoft Excel Worksheet.xlsx
	O	P	Q	R	S	T	U	V	W
1			1	XS	S	M	L	XL	XXL
2			2	OS
3			3	36	38	40	42	44	46
4			4	30	30.5	31	31.5	32	32.5
5			tag	quantity	quantity	quantity	quantity	quantity	quantity
6	SKU1		4	30-0	30.5-1	31-5	31.5-1	32-3	32.5-2
Sheet3

Thanks again!

 

[SanjayGMusafir]

Check this and revert -

Book1
	A	B	C	D	E	F	G	H	I
1			1	XS	S	M	L	XL	XXL
2			2	OS
3			3	36	38	40	42	44	46
4			4	30	30.5	31	31.5	32	32.5
5			tag	quantity	quantity	quantity	quantity	quantity	quantity
6	SKU1		4	0	1	5	1	3	2
7	SKU2		2	1
8	SKU3		3	4	0	6	0	0	0
9	SKU4		1	0	5	6	0	0	0
10
11	SKU1		4	30-0	30.5-1	31-5	31.5-1	32-3	32.5-2
12
13
14	wanted output:		Tag	4
15			A1+"-"+tag identifier			quantity
16			SKU1-30	SKU1-30	0	0
17			SKU1-30.5	SKU1-30.5	1	1
18			SKU1-31	SKU1-31	5	5
19			SKU1-31.5	SKU1-31.5	1	1
20			SKU1-32	SKU1-32	3	3
21			SKU1-32.5	SKU1-32.5	2	2
Sheet1

Cell Formulas
Range	Formula
A11,C11	A11	=A6
D11:I11	D11	=LET(Sz,FILTER($D$1:$I$4,$C$1:$C$4=$C$11),Qty,FILTER($D$6:$I$9,$C$6:$C$9=$C$11),Sz&"-"&Qty)
D16:D21	D16	=LET(SKU,XLOOKUP($D$14,$C$6:$C$9,$A$6:$A$9),Sz,FILTER($D$1:$I$4,$C$1:$C$4=$D$14),TRANSPOSE(SKU&"-"&Sz))
E16:E21	E16	=TRANSPOSE(FILTER($D$6:$I$9,$C$6:$C$9=$D$14))
Dynamic array formulas.

 

[yuvalshabt]

The GOAT right here!
Thank you very much!
DAYS of processing are saved here with this!

Thanks again!!!

 

[SanjayGMusafir]

Glad to help you and thanks for the feedback.

 

[yuvalshabt]

Hi, I tried it on my real file but ran into a problem.
I didnt change the "sizes" for the rest of the skus with different "tag" number.
It just continues to run the first tag sizes it found in the first column and doesnt "drag" all the way down for the entire table.
Any help will be much apricated!

BRUNELLO WOMENS MASTER 01.30.24.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG
1		01	XS	S	M	L	XL	XXL	XXX	4X	5X
2		05	OS
3		12	36	38	40	42	44	46	48	50	52	54
4		14	35	35½	36	36½	37	37½	38	38½	39	39½	40	40½	41	41½	42
5		16	XXS	XS	S	M	L	XL	XXL	XXX	4X	5XL
6		17	XXS	XS	S	M	L	XL	XXL	3XL	4XL	5XL	6XL
7		23	34	36	38	40	42	44	46	48	50	52	54	56
8		39	S	M	L
9	COMPOSIZIONE	Ttg	.	.	.	.	.	.	.	.	.	.	.	.	.	.	.
10	sku1	17				2												17	XXS-	XS-	S-	M-2	L-	XL-	XXL-	3XL-	4XL-	5XL-	6XL-	-	-	-	-
11	sku2	23				2												23	XXS-	XS-	S-	M-3	L-	XL-	XXL-	3XL-	4XL-	5XL-	6XL-	-	-	-	-
12	sku3	14				1												14	XXS-4	XS-7	S-	M-4	L-5	XL-2	XXL-	3XL-	4XL-	5XL-	6XL-	-	-	-	-
13	sku4	17				3												17
14	sku5	17	4	7		4	5	2										17
WOMAN

Cell Formulas
Range	Formula
S10:AG12	S10	=LET(Sz,FILTER(WOMAN!$C$1:$Q$8,WOMAN!$B$1:$B$8=R10),Qty,FILTER(WOMAN!$C$10:$Q$14,WOMAN!$B$10:$B$14=R10),Sz&"-"&Qty)
R10:R14	R10	=B10
Dynamic array formulas.

 

[SanjayGMusafir]

You are using wrong cell referencing. Try changing to...

=LET(Sz,FILTER(WOMAN!$C$1:$Q$8,WOMAN!$B$1:$B$8=$R$10),Qty,FILTER(WOMAN!$C$10:$Q$14,WOMAN!$B$10:$B$14=$R$10),Sz&"-"&Qty)