CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formula
Daniel
Daniel

Reputation: 15

Split Google Sheets Columns into rows - ignoring any blanks

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)

Upvotes: 0

Views: 618

Answers (1)

kirkg13
kirkg13

Reputation: 3010

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.

Upvotes: 1

Related Questions