Returning a concatenated list for matching criteria in excel-CodePudding

I have a list of requirements matched to the projects in a matrix-like structure, below.

         PJ 1 PJ2  PJ3  ...
Req 1     x    x     x
Req 2     x          x    
Req 3     x    x 
 ...

In another sheet, I have the list of requirements (not in the same order). In the column next to these requirements, I would like to return the PJs that have matched with "x". So results should look like this:

Req 1: Pj1 PJ2 PJ3
Req 2: PJ1 PJ3
Req 3: PJ1 PJ2

Any suggestion on how to get these projects as a horizontal list next to requirements, without gaps in between?

CodePudding user response：

If I have understood your requirements correctly, then the output should be like this

• Formula used in cell G2

=ARRAYTOTEXT(FILTER(B$1:D$1,TOROW(MMULT(TRANSPOSE(($F2=$A$2:$A$4)*($B$2:$D$4="x")),{1;1;1}))))

Or, may be using MAP() Function

• Formula used in cell G6

=MAP(F6:F8,LAMBDA(m,
CONCAT(FILTER(B1:D1,TOROW(MMULT(
TRANSPOSE((A2:A4=m)*(B2:D4="x")),{1;1;1}))))))

CodePudding user response：

My approach is in F6 cell

=TEXTJOIN(",",,IF(OFFSET($A$1,MATCH(E6,$A$2:$A$4,FALSE),1,,3)="x",$B$1:$D$1,""))

CodePudding user response：

Like Tom Sharp asked: Do you want the results for each row in a single cell or in separate cells?

I decided on posting a variation on solutions you may want.

(For each solution I created a dynamic way of dealing with your range. If the range changes, all you need to update is the (full) range in the formula; the rest will adopt to this range):

BYROW (1)

=LET(range, A1:E6,
     req,   DROP(TAKE(range,,1),1),
     PJ,    DROP(TAKE(range,1),,1),
     data,  DROP(range,1,1),
res,BYROW(data,LAMBDA(x,TEXTJOIN(" ",1,FILTER(PJ,x="x","")))),
HSTACK(req,res))

This solution makes use of BYROW to be able to use TEXTJOIN row-wise. The Req data is in the first column and the byrow/textjoin result in the next column.

BYROW (2)

=LET(range, A1:E6,
     req,   DROP(TAKE(range,,1),1),
     PJ,    DROP(TAKE(range,1),,1),
     data,  DROP(range,1,1),
res,BYROW(data,LAMBDA(x,TEXTJOIN(" ",1,FILTER(PJ,x="x","")))),
req&": "&res)

Does the same as the BYROW (1)-solution, however the Req is joined in front of the row-wise textjoin result.

REDUCE

=LET(Range, A1:E6,
     Req,   DROP(TAKE(Range,,1),1),
     PJ,    DROP(TAKE(Range,1),,1),
     data,  DROP(Range,1,1),
Res,DROP(IFERROR(REDUCE(0,SEQUENCE(ROWS(Req)),LAMBDA(x,y,VSTACK(x,FILTER(PJ,INDEX(data,y,)="x","")))),""),1),
HSTACK(Req,Res))

This solution allows you to spill the results to the right. Each result has it's own cell, filtering out the non-"x" values per row.