Cross sorting arrays

Question

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

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

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))

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.

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,,""))