Consolidate Data and Sum Value

[JMJ]

Hi, I would like to take the data in the yellow table and make it look like the green table. I tried using Consolidate, but I lose the Rep column and the Unit Price column gets summed. Product, Rep, and Unit Price could be considered a unique identifier. For all the rows in which they are the same, I only need them listed once and would like a their Quantity and Total Cost to be summed as shown in the green table. I don't work with excel much and haven't found an answer to this specific question online. I am using Excel 365.
Thanks for any help you can provide!

 

[lrobbo314]

No problem. Having fun with the dynamic array formulas.

Here is a version that does the totals for you as well.

Book One.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1	Product	Rep	Unit Price	Quantity	Total Cost		Product	Rep	Unit Price	Quantity	Total Cost
2	Product 1	Jill	150	1	150		Product 1	Jill	150	5	750
3	Product 1	Jill	150	1	150		Product 2	Jill	200	2	400
4	Product 1	Jill	150	3	450		Product 2	Scott	100	5	500
5	Product 2	Jill	200	2	400		Product 1	Bill	50	3	150
6	Product 2	Scott	100	3	300		Product 3	Bill	25	7	175
7	Product 2	Scott	100	1	100			Totals:	525	22	1,975
8	Product 2	Scott	100	1	100
9	Product 1	Bill	50	3	150
10	Product 3	Bill	25	1	25
11	Product 3	Bill	25	3	75
12	Product 3	Bill	25	3	75
Sheet4

Cell Formulas
Range	Formula
G2:K7	G2	=LET(ft,UNIQUE(TBL[[Product]:[Unit Price]]),pc,TBL[Product],rc,TBL[Rep],qc,TBL[Quantity],tc,TBL[Total Cost],up,INDEX(ft,,1),ur,INDEX(ft,,2),tq,SUMIFS(qc,pc,up,rc,ur),tot,SUMIFS(tc,pc,up,rc,ur),IFERROR(INDEX(CHOOSE({1,1,1,2,3},ft,tq,tot),SEQUENCE(ROWS(ft)+1),{1,2,3,4,5}),CHOOSE({1,2,3,4,5}," ","Totals: ",SUM(ft),SUM(tq),SUM(tot))))
Dynamic array formulas.