Split Google Sheets Columns into rows - ignoring any blanks

Question

I have a range of columns that include sizes, which vary between products, so there are quite a lot of blanks on each row. I have used this formula so far, to select 5 columns and convert them into rows (column DK/DL in the file below). I need to extend this to feature more rows, but it is too big when all the blanks are included, so is there a way to just ignore any blank cells and just include cells which have a value?

=ARRAYFORMULA(TRIM(SPLIT(TRANSPOSE(SPLIT(QUERY(TRANSPOSE(QUERY(TRANSPOSE(IF(LEN(A3:A), "♠"&A3:A&"♦"&AP3:AT, )), , 999^99)), , 999^99), "♠")), "♦")))

I also need to display the 2nd row next to this in a column. So for example, I have used columns AP - AT, which include XS, S, M, L, XL as column headers and then there is a value inside these on each row. The formula above will grab the values from those 5 columns and will put them on their own row. Is there a way to get the next column to just display the column headers? as when i reuse the formula aboe again it then drops to the row below and uses stock numbers instead of the headers again.

Effectively I am wanting to go from:

SKU | XS | S | M | L | XL

val1| 12 | 3 | 32| 1 | 123

To this:

Val1 | 12 | XS

Val1 | 3 | S

Val1 | 32 | M

Val1 | 1 | L

Val1 | 123| XL

Link to a google sheet test : https://docs.google.com/spreadsheets/d/1o7zFmPbIkKz5JiZ-7Blc-63pARfvhhaV9lWnL4kUIfY/edit?usp=sharing

Update - answered The best answer for this is in the comments below.

=query( {ARRAYFORMULA(TRIM(SPLIT(TRANSPOSE(SPLIT(QUERY(TRANSPOSE(QUERY(TRANSPOSE(IF(LEN(A3:A), "♠"&A3:A&"♦"&AN3:AU, )), , 999^99)), , 999^99), "♠")), "♦"))), query(ARRAYFORMULA(TRIM(SPLIT(TRANSPOSE(SPLIT(QUERY(TRANSPOSE(QUERY(TRANSPOSE(IF(LEN(A3:A), "♠"&A3:A&"♦"&AN2:AU2, )), , 999^99)), , 999^99), "♠")), "♦"))),"select Col2",0)}, "select * where Col2 <>''",0)

Are you averse to using Apps Script? What are the ♠ ♦ about in the formula? Also, try and put code in code blocks in future - stackoverflow.com/help/formatting. — iansedano
I ask about Apps Script because it seems like the formula is getting quite complex and you have a lot of data. Maybe possible with formulas, but with Apps Script definitely possible and much easier to manage. — iansedano
@iansedano I wasn't using apps script but I can do. Those characters where included in a formula script which I used to get a start with. The only part I need to do now, is to include from column 'I' to column 'CU' but ignore any cells that are blank. When I try to add a filter, it breaks the formula.. What would I need to do with Apps Script? — Daniel

kirkg13 kirkg13 · Accepted Answer · 2020-11-04T15:58:10

I'm not sure if I understand exactly what you want. But is it something like the result from this formula? Place it in cell DP3 of your sample sheet.

=query(ArrayFormula(if(len(DL3:DL),DK3:DK&" ~ "&DK3:DL&" ~ "&DN3:DN,"")),"select Col2 where Col2 <>''",0)

It results in the following on the first row: 645550-010 ~ 0 ~ M

The separators are just for clarity, and can be removed of course.

This is built using the data in columns DK-DN, and effectively dropping rows with no values in column DL. It is possible/likely that you want the solution formula to be incorporated with the formulas that currently build columns DK-DN, so that they are no longer necessary?

If I'm on the wrong track, I apologise. Then could you please explain in more detail what you want in what cells, and how that result is built from values in which columns.

UPDATE: The following version of the formula splits the result into three columns, instead of all being in one column.

=query(ArrayFormula(if(len(DL3:DL),{DK3:DK,DK3:DL,DN3:DN},{"","",""})),"select Col2, Col3, Col4 where Col2 <>''",0)

Note that this seems to also work without the {"","",""} as the second part of the IF statement, but I'm not sure on the detaails of what is happening there, so left it in.

UPDATE #2:

The final formula that met the requirements of the question is as follows:

=QUERY({
         ARRAYFORMULA(TRIM(SPLIT(TRANSPOSE(SPLIT(QUERY(TRANSPOSE(QUERY(TRANSPOSE(IF(LEN(A3:A), "♠"&A3:A&"♦"&AN3:AU,  )), , 999^99)), , 999^99), "♠")), "♦"))),
   query(ARRAYFORMULA(TRIM(SPLIT(TRANSPOSE(SPLIT(QUERY(TRANSPOSE(QUERY(TRANSPOSE(IF(LEN(A3:A), "♠"&A3:A&"♦"&AN2:AU2, )), , 999^99)), , 999^99), "♠")), "♦"))),
     "select Col2",0)
   },
   "select * where Col2 <>''",0)

This basically uses the two original queries that produced some temporary working columns, and filters out the blank values as was requested. Without the need for the temporary columns, which held data containing blank cells, several thousand rows can be deleted from the spreadsheet, improving performance.

Split Google Sheets Columns into rows - ignoring any blanks

1 Answers