The Problem
I am trying to find a way to simplify data in multiple columns. Some columns have duplicate COLOUR values with a unique ID and a corresponding QTY. The ideal result would be a concatenated ID (based on duplicates), a sum of the QTY (based on duplicates) and consolidation of COLOUR (removing duplicates). Everything I have found so far doesn't quite fit the scenario and the expected outcome in the table below. Whatever the solution may be, there will be hundreds of unique entries in the ID column and COLOUR column so the solution must take that into consideration. Any help would be appreciated.
Sample Data
| ID | COLOUR | QTY |
|---|---|---|
| A1 | BLUE | 1 |
| B1 | GREEN | 2 |
| A2 | BLUE | 1 |
| A3 | BLUE | 1 |
| B2 | GREEN | 1 |
Expected Outcome
| ID | COLOUR | QTY |
|---|---|---|
| A1, A2, A3 | BLUE | 3 |
| B1, B2 | GREEN | 3 |
CodePudding user response：
So, assuming you are using MS365 then you may try using the formula as shown below,
• Formula used in cell E2
=LET(U,UNIQUE(B2:B6),CHOOSE({1,2,3},
BYROW(U,LAMBDA(a,TEXTJOIN(",",,FILTER(A2:A6,a=B2:B6,"")))),
U,BYROW(U,LAMBDA(a,SUM(FILTER(C2:C6,a=B2:B6,""))))))
Also when writing this formula, if you are on MS365 and have enabled the Beta Channel then using VSTACK() & HSTACK()
• Formula used in cell I1
=LET(U,UNIQUE(B2:B6),VSTACK(A1:C1,HSTACK(
BYROW(U,LAMBDA(a,TEXTJOIN(",",,FILTER(A2:A6,a=B2:B6,"")))),
U,BYROW(U,LAMBDA(a,SUM(FILTER(C2:C6,a=B2:B6,"")))))))
Or, you can follow as 
• Formula used in cell E2
=LET(X,UNIQUE(B2:B6),CHOOSE({1,2,3},BYROW(X,LAMBDA(a,
TEXTJOIN(", ",,FILTER(A2:A6,B2:B6=a)))),X,SUMIF(B2:B6,X,C2:C6)))