Query one table and output data into multiple colu

问题:

Simply put I am trying to take a single column query result and output it into a 5 wide by × long table. This is how the main table is organized.

On separate tabs, I want to list all of the caught and seen Pokemon on their own for easy search. While I can get it to output something like this with

=query(NatDex, "Select C Where F <> ''",1) 

I would like it to output the data something like this for easy reading so it's not eventually 100+ entries long:

Bonus points if you can give me formula/something to do it where I can vary how wide the second table is. But this is far less important to me. I've tried looking up stuff like Pivot tables or Transpose, but neither of them seems to have the functions I need to pull this off.

回答1:

if you put your query output in some auxiliary column, you can use this formula and drag down:

=ARRAY_CONSTRAIN(TRANSPOSE(INDIRECT("A"&6+(ROW()-ROW($A$2))*5&":A")), 1, 5)

---

for 6 columns:

=ARRAY_CONSTRAIN(TRANSPOSE(INDIRECT("A"&7+(ROW()-ROW($A$2))*6&":A")), 1, 6)

for 3 columns:

=ARRAY_CONSTRAIN(TRANSPOSE(INDIRECT("A"&4+(ROW()-ROW($A$2))*3&":A")), 1, 3)

for 5 columns but starting on 10th row:

=ARRAY_CONSTRAIN(TRANSPOSE(INDIRECT("A"&6+(ROW()-ROW($A$2)-9)*5&":A")), 1, 5)

etc.