How can I concatenate one column based on whether or not their is duplicate values in another column and add their quantity column?

user_14910567 0
Viewed 51

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
excel-formula excel
1 Answers

So, assuming you are using MS365 then you may try using the formula as shown below,

FORMULA_SOLUTION

• 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_SOLUTION

• 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 JvdV Sir has mentioned, simplifying the formula by removing the 2nd BYROW() with SUMIF()

FORMULA_SOLUTION

• 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)))
user_81625200
Related