Cross sorting arrays

Question

I have a table that list the tickets with a list of tests attached:

enter image description here

And I want to get the list of test with the tickets related to this test:

enter image description here

I'm using Excel Office 365 and the dynamics arrays, so for this example, I will have for the first entire array A1#.

Then, now I have the list of the tests by using this formula :

[in french]

=EXCLURE(TRIER(UNIQUE(DANSCOL(EXCLURE(tickets;;1)));;1);-1) 

[in english]

=EXCLUDE(SORT(UNIQUE(BYCOL(EXCLUDE(A1#,,1))), 1), -1)

But I'm stuck on the generation of the second part to get the lists of the tickets by tests

I have tried the BYROW formula or the XLOOKUP, without success.

Is it possible to manage it only by formula?

Answer 1

Transform Data: Switch Values and Row Labels

• It is assumed that the initial row labels (A1:A5) are distinct.
• It is assumed that the initial values (B1:D5) are distinct per row.

=LET(data,A1:D5,
 sl,TAKE(data,,1),
 sv,DROP(data,,1),
 dl,UNIQUE(TOCOL(IFS(sv<>"",sv),2)),
 dv,IFNA(DROP(REDUCE("",dl,LAMBDA(rr,r,
 VSTACK(rr,TOROW(IFS(ISNUMBER(SEARCH(r,sv)),sl),2)))),1),""),
 HSTACK(dl,dv))

enter image description here

Edit

• Mayukh Bhattacharya's first formula is more accurate (SEARCH might encounter false positives) and reduces TOROW(IFS(ISNUMBER(SEARCH(r,sv)),sl),2) to TOROW(IFS(sv=r,sl),2) in the formula above.

• MonkeyJLuffy: Well, it return N/A in the second colums. Formula used : =LET( sl; PRENDRE(test;;1); sv; EXCLURE(test;;1); dl; UNIQUE(DANSCOL(SI.CONDITIONS(sv<>"";sv);2)); dv; SI.NON.DISP(EXCLURE(REDUCE(""; dl; LAMBDA(rr;r; ASSEMB.V(rr; DANSLIGNE( SI.CONDITIONS( ESTNUM( RECHERCHE( r; sv)); sl );2))));1);""); ASSEMB.H(dl;dv) )
• VBasic2008: The edit at the bottom means instead of DANSLIGNE( SI.CONDITIONS( ESTNUM( RECHERCHE( r; sv)); sl );2) use DANSLIGNE(SI.CONDITIONS(sv=r;sl);2). To be able to use A1# (probably test), A1:D5 must be the result (spill) of another (array) formula.

Answer 2

If it's not a large array, this mat suit your needs:

=LET(a,DROP(A1#,,1),L,LAMBDA(x,TOCOL(IFS(a>"",x),2)),TEXTSPLIT(CONCAT(BYROW(GROUPBY(L(a)&", ",L(TAKE(A1#,,1)),ARRAYTOTEXT,,0),CONCAT)&"|"),", ","|",1,,""))

• MonkeyJLuffy: This result is close to that I need, but the result is in two colomns, and the second one have the result separate by ', '. formula used : =LET( _a; EXCLURE(test;;1); Lam;LAMBDA(x; DANSCOL(SI.CONDITIONS(_a>"";x);2)); FRACTIONNER.TEXTE(CONCAT(BYROW(GROUPER.PAR(Lam(_a) & ", "; Lam(PRENDRE(test;;1)); TABLEAU.EN.TEXTE;;0);CONCAT) & "|"); ", "; "|"; 1;;"") )
• P.b: So you're helped or need something else?
• MonkeyJLuffy: Thanks, I have my solution, I marked it. So is ok for me now.

Answer 3

Try using the following formula:

enter image description here

=TOROW(IFS(A7=B1ドル:D5,ドルA1ドル:A5ドル),2)

---

Also, if applicable using PIVOTBY()

enter image description here

=LET(
 _a, B1:D5,
 _b, TOCOL(IFS(_a<>"",A1:A5),2),
 _c, SEQUENCE(ROWS(_b)),
 _d, TOCOL(_a, 1),
 DROP(PIVOTBY(_d, MAP(_d, _c, LAMBDA(x,y, SUM((_d=x)*(_c<=y)))), _b, SINGLE,,0,,0),1))

• MonkeyJLuffy: The first formul doesn't fit that I need because I cannot extend then using BYROW. The second one works well with this data test. But doesn't work when I replace the value with the original ones : DA-7010 DA-5460 DA-6948 DA-7004 DA-5475 DA-6948 DA-7000 DA-7013 DA-7007 DA-6998 DA-6057 DA-6948 DA-6997 DA-5430 DA-6948 DA-6993 DA-6066 DA-6948
• Mayukh Bhattacharya: What do you mean by replace the value ? Where are you replacing, which part are you replacing, kindly edit your post to show the examples
• MonkeyJLuffy: well, I don't understant what happend exactly.
• MonkeyJLuffy: Here the formula : =LET( _a;EXCLURE(tickets;;1); _b;DANSCOL(SI.CONDITIONS(_a<>"";PRENDRE(tickets;;1));2); _c;SEQUENCE(LIGNES(_b)); _d;DANSCOL(_a;1); EXCLURE(PIVOTER.PAR(_d; MAP(_d; _c; LAMBDA(x;y; SOMME((_d=x)*(_c<=y)))); _b; SINGLE;;0;;0);1))
• MonkeyJLuffy: Well, I don't understand exactly what happened. I created a small array, selected it, and named it "tickets". It worked perfectly with your formula—like magic! However, if I replace the name "tickets" with another name or with a reference like SheetWithValue!A1#, it returns a #VALUE! error. It seems like the issue is related to how the PIVOTBY function handles the reference.

Answer 4

Another solution:

=IFNA(TEXTSPLIT(TEXTJOIN("*";FALSE;MAP(UNIQUE(TOCOL(B1:D5;1));
 LAMBDA(x;TEXTJOIN("=";TRUE;x;IF(B1:D5=x;A1:A5;"")))));"=";"*");"")