0

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

enter image description here

Osm
  • 2,699
  • 2
  • 4
  • 26
  • This is basically a unpivot or reverse pivot after a transpose. There are plenty of ways to do this. If you use my named function [here](https://stackoverflow.com/a/55869948/)(written in formulas alone), this is simple as `=UNPIVOT(TRANSPOSE(A1:C4),1)` – TheMaster Oct 04 '22 at 14:10
  • If the linked duplicate answers doesn't answer your question, [edit] your question to explain how the answers don't satisfy your question. Note that there's a proper [etiquette](https://meta.stackoverflow.com/questions/252252) to dispute a duplicate. If you're having trouble implementing a solution in the duplicate or don't understand a solution provided, ask a new specific question with the part you're having trouble with. – TheMaster Oct 04 '22 at 14:10
  • @TheMaster this is unlinked columns you [answer](https://stackoverflow.com/a/55869948/19529694) does't produce the desired result [see](https://i.imgur.com/L9IvrNP.png),.... i did set s_col to `1`, and the same can be said for other answers. – Osm Oct 04 '22 at 14:25
  • As said in [my first comment](https://stackoverflow.com/questions/73948835/convert-a-multi-list-to-a-list-of-category-items-in-google-sheets?noredirect=1#comment130570787_73948835), `TRANSPOSE` the `range` first.(Also, if you want to dispute the duplicate, [edit] your question to clarify as it helps others to weigh in the closure vote) – TheMaster Oct 04 '22 at 14:51
  • @TheMaster It [works](https://i.imgur.com/CW4jNBl.png) when trasposed, but I already solve it with one refrence and no need to specify columns "s_col" , thanks. – Osm Oct 04 '22 at 14:59
  • The underlying technique is still `unpivot`(whether transposed or otherwise). You're free to add a answer in the linked duplicate post there, if you have a different method and the community might appreciate your method, if it's easier than the rest. – TheMaster Oct 04 '22 at 15:02
  • still looking for a solution? – player0 Oct 04 '22 at 15:56
  • @Player0 I created a sloution, I need to open this question to share it, the suggested duplicate is different than this question but I managed to use this [comment](https://stackoverflow.com/questions/73948835/convert-a-multi-list-to-a-list-of-category-items-in-google-sheets?noredirect=1#comment130570787_73948835) – Osm Oct 04 '22 at 16:30

2 Answers2

0

try:

=INDEX(SORT(QUERY(SPLIT(FLATTEN(A1:C1&"×"&A2:C), "×"), "where Col2 is not null", )))

enter image description here

player0
  • 124,011
  • 12
  • 67
  • 124
  • Use `QUERY(range,"Limit 1")` And `QUERY(range,"Offset 1")` to do it with one reference – Osm Oct 04 '22 at 16:35
0

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

enter image description here

Used formulas help
ARRAYFORMULA - LAMBDA - QUERY - SPLIT - FLATTEN - TRANSPOSE - BYCOL - TEXTJOIN - OFFSET - IF

Osm
  • 2,699
  • 2
  • 4
  • 26