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:

 

[yuvalshabt]

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)

Thank you, I'll share the original as there is something that I'm missing here 100% as there are 2 issues:
1. It doesnt go all the way down the rows (specifically in this shorter version of the file)
2. it doesnt change once the tag changes (from 17 to 13 for this example)

BRUNELLO WOMENS MASTER 01.30.24(1).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	AH	AI
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													25
9	COMPOSIZIONE	Ttg	.	.	.	.	.	.	.	.	.	.	.	.	.	.	.	TOTALE	MSRP
10	sku1	17				2												2	$ 5,895.00	17	XXS-	XS-	S-	M-2	L-	XL-	XXL-	3XL-	4XL-	5XL-	6XL-	-	-	-	-
11	sku2	17				2												2	$ 5,895.00	17	XXS-	XS-	S-	M-2	L-	XL-	XXL-	3XL-	4XL-	5XL-	6XL-	-	-	-	-
12	sku3	17				1												1	$ 3,695.00	17
13	sku4	23				1	1											2	$ 1,150.00	23
14	sku5	23				1	2											3	$ 2,795.00	23
15	sku6	23			1	2	6	1		1								11	$ 1,295.00	23
16	sku7	23					2											2	$ 1,395.00	23
17	sku8	23					2											2	$ 1,150.00	23
WOMAN

Cell Formulas
Range	Formula
R8	R8	=SUM(R10:R17)
U10:AI11	U10	=LET(Sz,FILTER(WOMAN!$C$1:$Q$8,WOMAN!$B$1:$B$8=$T$10),Qty,FILTER(WOMAN!$C$10:$Q$17,WOMAN!$B$10:$B$17=$T$10),Sz&"-"&Qty)
T10:T17	T10	=B10
Dynamic array formulas.

 

[SanjayGMusafir]

I just revert, the solution I posted needs to be re-edited

 

[SanjayGMusafir]

Check this and revert -

Book1
	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	AH	AI
1		1	XS	S	M	L	XL	XXL	XXX	4X	5X
2		5	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													25
9	COMPOSIZIONE	Ttg	.	.	.	.	.	.	.	.	.	.	.	.	.	.	.	TOTALE	MSRP
10	sku1	12				2												2	5895	12	36 -	38 -	40 -	42 - 2	44 -	46 -	48 -	50 -	52 -	54 -	-	-	-	-	-
11	sku2	16				2												2	5895	16	XXS -	XS -	S -	M - 2	L -	XL -	XXL -	XXX -	4X -	5XL -	-	-	-	-	-
12	sku3	16				1												1	3695	16	XXS -	XS -	S -	M - 1	L -	XL -	XXL -	XXX -	4X -	5XL -	-	-	-	-	-
13	sku4	5				1	1											2	1150	5	OS -	-	-	- 1	- 1	-	-	-	-	-	-	-	-	-	-
14	sku5	23				1	2											3	2795	23	34 -	36 -	38 -	40 - 1	42 - 2	44 -	46 -	48 -	50 -	52 -	54 -	56 -	-	-	-
15	sku6	39			1	2	6	1		1								11	1295	39	S -	M -	L - 1	- 2	- 6	- 1	-	- 1	-	-	-	-	-	-	-
16	sku7	23					2											2	1395	23	34 -	36 -	38 -	40 -	42 - 2	44 -	46 -	48 -	50 -	52 -	54 -	56 -	-	-	-
17	sku8	23					2											2	1150	23	34 -	36 -	38 -	40 -	42 - 2	44 -	46 -	48 -	50 -	52 -	54 -	56 -	-	-	-
Sheet1

Cell Formulas
Range	Formula
R8	R8	=SUM(R10:R17)
T10:T17	T10	=B10
U10:AI17	U10	=LET(Sz,FILTER($C$1:$Q$8,$B$1:$B$8=$T10,""),Qty,$C10:$Q10,Sz&" - "&Qty)
Dynamic array formulas.

 

[yuvalshabt]

amazing! a true master! thank you very much!!!

 

[SanjayGMusafir]

Glad to help you and thanks for the feedback.