CODEWITHSUNDEEP
google-sheetstransposegoogle-sheets-formulaarray-formulasgoogle-sheets-query
MiuKujo
MiuKujo

Reputation: 243

Query one table and output data into multiple columns

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.

Main table

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)

Vertical Table

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

Horizontal Table

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.

Upvotes: 1

Views: 1046

Answers (1)

player0
player0

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

enter image description here

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.

Upvotes: 1

Related Questions