Easy Formula - Pros Please help :)

[GLOJACK]

Hello I am looking for help with a formula . I have a list of multiple ready dates and quantities for a single part, example

Part ABC 25pcs 2/21
Part ABC 13pcs 1/21
Part ABC 5pcs 1/3
Part BCD 30pcs 1/24
Part BCD 30pcs 3/2

I rolled up it to a pviot table , I am looking to create a formula where it can sum all the dates and qtys into one row so I can do a vlookup and for example it will say for part ABC : 25pcs 2/21, 13pcs 1/21, 5pcs 1/3

I was trying to do an if formula w/ concatenate based on the below pivot table... anyone know a better way to do this ?
Pivot example

Part #	Sum of Qty Committed
ABC	36​
02-21	25​
01-03	11​
BCD	75​
02-21	30​
02-14	15​
01-24	15​
DCB	15​
01-03	5​
01-17	10​

 

[GLOJACK]

Or via the horizontal pivot ?

Part #	01-02	01-03	01-24	02-21	02-14	02-21
ABC		11​				25​
BCD			15​	15​	15​	30​
DCB		5​

 

[StephenCrump]

Will this work for you?

	A	B	C	D	E	F
1	Part	Pcs	Date
2	ABC	25	Feb 21
3	ABC	13	Jan 21
4	ABC	5	Mar 23		ABC	25 pcs 2/1,13 pcs 1/1,5 pcs 3/1
5	BCD	30	Jan 24		BCD	30 pcs 1/1,30 pcs 2/3
6	BCD	30	Feb 23
7
Sheet1

Cell Formulas
Range	Formula
E4:E5	E4	=UNIQUE(A2:A6)
F4:F5	F4	=BYROW(E4#,LAMBDA(r,TEXTJOIN(",",,FILTER(B2:B6&" pcs "&TEXT(C2:C6,"m/d"),A2:A6=r))))
Dynamic array formulas.

 

[GLOJACK]

Will this work for you?

	A	B	C	D	E	F
1	Part	Pcs	Date
2	ABC	25	Feb 21
3	ABC	13	Jan 21
4	ABC	5	Mar 23		ABC	25 pcs 2/1,13 pcs 1/1,5 pcs 3/1
5	BCD	30	Jan 24		BCD	30 pcs 1/1,30 pcs 2/3
6	BCD	30	Feb 23
7
Sheet1

Cell Formulas
Range	Formula
E4:E5	E4	=UNIQUE(A2:A6)
F4:F5	F4	=BYROW(E4#,LAMBDA(r,TEXTJOIN(",",,FILTER(B2:B6&" pcs "&TEXT(C2:C6,"m/d"),A2:A6=r))))
Dynamic array formulas.

Its perfect thank you ! You are a genius I could not have come up with such a perfect formula. Thank you !!!

 

[GLOJACK]

Will this work for you?

	A	B	C	D	E	F
1	Part	Pcs	Date
2	ABC	25	Feb 21
3	ABC	13	Jan 21
4	ABC	5	Mar 23		ABC	25 pcs 2/1,13 pcs 1/1,5 pcs 3/1
5	BCD	30	Jan 24		BCD	30 pcs 1/1,30 pcs 2/3
6	BCD	30	Feb 23
7
Sheet1

Cell Formulas
Range	Formula
E4:E5	E4	=UNIQUE(A2:A6)
F4:F5	F4	=BYROW(E4#,LAMBDA(r,TEXTJOIN(",",,FILTER(B2:B6&" pcs "&TEXT(C2:C6,"m/d"),A2:A6=r))))
Dynamic array formulas.

Sorry I need some addt help, it worked in my test spreadheet but now when I try to use it in my report I am getting the below error, seems to be the r it doesnt like and when I remove the r I get a name error

 

[StephenCrump]

I'm not sure what you mean about removing r? Presumably before that, you're getting a #REF! error, as shown, because your formula is pointing to an array A2# which doesn't exist.

	A	B	C	D	E	F	G	H	I
1
2	A	11	29 Dec 2023					#REF!	11 pcs 12/29,15 pcs 2/2
3	B	25	16 Feb 2024						25 pcs 2/16,15 pcs 2/9
4	C	15	19 Jan 2024						15 pcs 1/19
5	A	15	2 Feb 2024						11 pcs 12/29,15 pcs 2/2
6	B	15	9 Feb 2024						25 pcs 2/16,15 pcs 2/9
Sheet1

Cell Formulas
Range	Formula
H2	H2	=BYROW(A2#,LAMBDA(r,TEXTJOIN(",",,FILTER(B2:B6&" pcs "&TEXT(C2:C6,"m/d"),A2:A6=r))))
I2:I6	I2	=BYROW(A2:A6,LAMBDA(r,TEXTJOIN(",",,FILTER(B2:B6&" pcs "&TEXT(C2:C6,"m/d"),A2:A6=r))))
Dynamic array formulas.

For just the uniques:

	A	B	C	D	E	F	G	H	I
1
2	A	11	29 Dec 2023					A	11 pcs 12/29,15 pcs 2/2
3	B	25	16 Feb 2024					B	25 pcs 2/16,15 pcs 2/9
4	C	15	19 Jan 2024					C	15 pcs 1/19
5	A	15	2 Feb 2024
6	B	15	9 Feb 2024
Sheet1

Cell Formulas
Range	Formula
H2:H4	H2	=UNIQUE(A2:A6)
I2:I4	I2	=BYROW(H2#,LAMBDA(r,TEXTJOIN(",",,FILTER(B2:B6&" pcs "&TEXT(C2:C6,"m/d"),A2:A6=r))))
Dynamic array formulas.

 

[GLOJACK]

My formula on my file pointed to the correct cell. Its weird, it worked fine on my home computer but wouldn't work on my work computer. Last night I took the file from my work computer and transferred it to my home and the formula was fine.

Then this AM did the same thing and moved the file to my work computer, it errors out the whole formula, do you think that maybe its the excel version ? On my work computer it takes the same file and adds a xlfn in front and changes it to a name error - see example below

 

[GLOJACK]

Ok seems its the excel version excel xlfn error " As per this Microsoft document the error appears when the workbook contains a function not available in your Excel version. The only solution is to remove unsupported functions (or updating your Excel)."