Excel Formula to populate table based on data obtained from another table with multiple drop down lists

Question

I'm looking for a formula to populate a table based on data obtained from another table that has multiple drop-down lists.

Any help will be greatly appreciated.

Answer 1

Try something along the lines of using without any recursive LAMBDA() with more efficiency.

---

• Formula used in cell A8

=TRIM(TEXTSPLIT(TEXTAFTER(" "&TOCOL(TOROW(B2:C2&" "&TOCOL(B3:C3))&" "&TOCOL(B4:D4))," ",{1,2,3})," "))

---

Or, bit more dynamic

---

=SORT(TEXTSPLIT(TEXTAFTER(" "&TOCOL(TOCOL(IFS(B2:E2<>"",B2:E2&" "&
 TOCOL(IFS(B3:E3<>"",B3:E3),3)),3)&" "&IFS(B4:E4<>"",B4:E4),3)," ",
 SEQUENCE(,MAX(TOCOL(IFS(B2:E4<>"",COLUMN(B2:E4)-1),3))))," "),3)

---

Answer 2

=DROP(REDUCE("",TOROW(B1:C1&"-"&TOCOL(B2:C2))&"-"&TOCOL(B3:D3),LAMBDA(a,b,VSTACK(a,TEXTSPLIT(b,"-")))),1)

Note that "-" delimiter is used to join and split. Choose it visely.

Result: