How to convert this unrelated multi-list "unrelated columns"
Category1 Category2 Category3 ...
Items... Items... Items... ...
| Bags | Shoes | Jewelry |
|---|---|---|
| Purses | Sneakers or tennis shoes | Necklaces |
| Shoulder bags | Flats | |
| Crossbody bag | Flip flops | Earrings |
To this, with one range reference? and filter empty rows.
| Bags | Purses |
|---|---|
| Bags | Shoulder bags |
| Bags | Crossbody bag |
| Shoes | Sneakers or tennis shoes |
| Shoes | Flats |
| Shoes | Flip flops |
| Jewelry | Necklaces |
| Jewelry | Earrings |
This is what i did so far
=ArrayFormula({
SPLIT(A1&" "&A2:A4," ");
SPLIT(B1&" "&B2:B4," ");
SPLIT(C1&" "&C2:C4," ")})
CodePudding user response：
try:
=INDEX(SORT(QUERY(SPLIT(FLATTEN(A1:C1&"×"&A2:C), "×"), "where Col2 is not null", )))
CodePudding user response：
I solved this problem with this formula:
LAMBDA names
range -->A1:C4
filter_empty --> takes 1 or 0
Set filter_empty 0 to keep empty rows or 1 to filter empty rows.
=ArrayFormula(
LAMBDA(range,filter_empty,
QUERY({
SPLIT(FLATTEN(SPLIT(TRANSPOSE(
BYCOL(range, LAMBDA(rg, TEXTJOIN("|",1,QUERY(rg,"Limit 1")&"#"&QUERY(rg,"Offset 1"))))), "|")),"#")},
" Select * Where Col2 <> '"& IF(filter_empty<>0,"","*")&"'"))(A1:C4,0))
Used formulas help
ARRAYFORMULA - LAMBDA - QUERY - SPLIT - FLATTEN - TRANSPOSE - BYCOL - TEXTJOIN - OFFSET - IF