Query one table and output data into multiple columns

Question

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.

player0 · Accepted Answer · 2019-02-21 15:38:59Z

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.

edited Feb 21, 2019 at 15:38

answered Feb 21, 2019 at 15:24

player0

131k14 gold badges92 silver badges149 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

MiuKujo Over a year ago

Worked perfectly, and the additional examples really helped show what I needed to change to get things lined up right.

Collectives™ on Stack Overflow

Query one table and output data into multiple columns

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related