How to stack different columns from a query into one single column Google Sheets

Question

I have done a QUERY from an importxml in Google Sheets, and I'm getting results in several columns. I need to get these results in only one column, but I always need to keep the first cell of each row even if it's blank. If that's the case, I don't want to keep blanks for other cells in the same row.

My data looks like this:

empty row
empty row
1x 1x 1x

1x 1x 1x
empty row
empty row
1x 1x 1x

1x 1x 1x
empty row
5x

And I want to get:

empty cell

empty cell
1x

1x

empty cell

5x

My current code is this: =QUERY(importxml('url-address';"//ul[@class='xxxxxxx']");"SELECT * WHERE Col1 CONTAINS 'x' OR Col1 IS NULL";-1)

EDIT: This is the link to the actual spreadsheet: https://docs.google.com/spreadsheets/d/1ZownNdUfFEeBmZ4hEQwkDVu-phYzU-2nCAH2Fg91GRs/edit?usp=sharing

I'm using to retrieve information for a videogame. I had already done it, but something has changed in the website I get the information from, and therefore now I need to adapt my formulae.

In tab "Gear components" you can find the structure of the data as it was with my previous formulae. In tab 'Formulas', you can find what I'm getting now. The list of gear on the left, and the amount of each one on the right, but with an inappropriate structure. Therefore, I need to keep all 1x and 5x.

I'm not sure I understand what is your desired output. Would you consider providing a copy of the spreadsheet, clarifying this? — Iamblichus
I'm not clear what your issue is. Is the importxml, by itself working fine? Your query seems reasonable. Possibly the -1 on the end could cause funny results, If your input data includes a header row, try 1 instead. Providing a sample sheet, with sample data, would let us help you. — kirkg13
And yes, you will need a formula, perhaps using REGEXREPLACE, to convert strings like "1x 1x 1x" to "1x". Is that what you are having a problem with? — kirkg13
I'm not sure I understand what the desired output is, even with the sample you provided. So, if a certain value had three 1x next to it, the output should repeat this value in three different rows, one with a 1x next to it? Something like this. — Iamblichus

Matt Pi Matt Pi · Accepted Answer · 2020-10-08T21:09:42

You can use =STACKARRAY().

It is not a native function so you have to install the add-on (it's completely free) https://gsuite.google.com/marketplace/app/stackarray_for_google_sheets/851782699334

Then you can use it this way: =STACKARRAY(ref_to_your_table) which will stack the 2nd column under the first column and so on...

You can also decide to remove blank rows: =STACKARRAY(ref_to_your_table, TRUE)

Or change the strategy for stacking =STACKARRAY(ref_to_your_table, TRUE or FALSE, TRUE)

How to stack different columns from a query into one single column Google Sheets

2 Answers