Converting column of various cells into matrix with conditions

Question

I am currently trying to convert a column of material codes of roughly 100 rows into a number of matrixes.

The size of matrix depends on another column setting the size.

Here is a scheme of what I am working with.

What I am trying to do with this table is to Group the similar "Mcode" together and make a matrix based on the conditions given in "Mcode 1st version", "Mcode last version", and "No. of versions in a row".

The example matrix should look something like this:

Because it's a first time I am encountering a complex issue like this I have no clue what to start with. Should I start with a pivot table? What is the best way to deal with this?

Answer 1

If I have understood clearly and not mistaken, then this should work for you

---

• Formula used in cell F2

=SORT(TEXTSPLIT(TEXTJOIN("/",,MAP(B2:B14,D2:D14,LAMBDA(m,q,
 ARRAYTOTEXT(LEFT(m)&TEXT(SEQUENCE(q,,MID(m,2,9)),"00"))))),", ","/",,,""))

---

Or,

---

• Formula used in cell F2

=SORT(IFERROR(TEXTSPLIT(TEXTAFTER(","&MAP(B2:B14,D2:D14,LAMBDA(m,q,
  ARRAYTOTEXT(LEFT(m)&TEXT(SEQUENCE(q,,MID(m,2,9)),"00")))),",",SEQUENCE(,MAX(D2:D14))),","),""))

---

Another alternative approach:

---

• Formula used in cell F2

=LET(
     a,B2:B14,
     b,D2:D14,
     SORT(MAKEARRAY(ROWS(a),MAX(b),LAMBDA(r,c,
     LET(z,INDEX(a,r),REPT(LEFT(z)&TEXT(MID(z,2,9)+(c-1),"00"),INDEX(b,r)>=c))))))

---

Another alternative approach:

---

• Formula used in cell F2

=LET(
     α, SORT(A2:D14),
     σ, INDEX(α,,2),
     φ, INDEX(α,,1),
     DROP(IFERROR(REDUCE("",σ,LAMBDA(x,y,VSTACK(x,TOROW(FILTER(φ,y=σ))))),""),1))

---

One more alternative approach:

---

• Formula used in cell F2

=LET(
     α, SORT(A2:D14),
     σ, INDEX(α,,2),
     φ, INDEX(α,,1),
     IFERROR(MAKEARRAY(ROWS(σ),MAX(INDEX(α,,4)),LAMBDA(_r,_c,
     INDEX(FILTER(φ,INDEX(σ,_r)=σ),_c))),""))

---