I have a data structure in excel where I want to generate a aggregated table based on a more detailed table based on some clustering e.g. Tasks -> Projects -> Investmentcluster.
In the aggregated projectoverview I use UNIQUE to get the list of Projects and then SUMIF and XLOOKUP to get sums or the first value from the original table columns I am interested in.
However I have one column, where I need all the entries beeing found concatinated. I would use something like concat(filter(l, x: x == "pName")) in programming languages. I already found the CONCAT function… but how do I get another function that returns a range I can feed into CONCAT?
Orignal Sheet
| Project | Task | Invest | Description |
|---|---|---|---|
| P1 | T1 | 1 | ABC |
| P1 | T2 | 1 | ABC |
| P1 | T3 | 1 | ABC |
| P1 | T4 | 1 | ABC |
| P2 | T5 | 1 | ABC |
| P2 | T6 | 1 | ABC |
| P2 | T7 | 1 | ABC |
Target Sheet
| Project | Invest | Description |
|---|---|---|
| P1 | 4 | ABC, ABC, ABC, ABC |
| P2 | 3 | ABC, ACB, ABC |
My problem lies in the Description column.
Greetings,
Xlaech
>Solution :
Have a go with:
Formula in F1:
=REDUCE(INDEX(A1:D1,{1,2,4}),UNIQUE(A2:A8),LAMBDA(x,y,VSTACK(x,HSTACK(y,SUMIF(A2:A8,y,C2:C8),TEXTJOIN(", ",,REPT(D2:D8,A2:A8=y))))))