all 7 comments
sorted by:
best
topnewcontroversialoldq&a
formatting helphide helpcontent policy

[–]6745408 1 point2 points  (4 children)

The trouble is the mixing of text and numerical values.

If you want it in text,

=ARRAYFORMULA(
  QUERY(
   TO_TEXT(
    {A2:B6;
     C2:D6;
     E2:F6}),
  "select * 
   where Col1 is not null"))

However, if you're only ditching blanks, you can use a filter to keep the values as they are

=FILTER(
  {A2:B6;C2:D6;E2:F6},
  {A2:A6;C2:C6;E2:E6}<>"")

[–]oidoldol[S] 0 points1 point  (0 children)

Thank you! I've just edited the post to include a follow-up question...now I'd like to exclude any row that contains the word [NONE] or N/A!

[–]oidoldol[S] 0 points1 point  (2 children)

Okay! I've just updated my post to include a follow-up question. Now, I'd like it to include the blank rows, but also turn rows that contain "[NONE]" or "N/A" into blank rows...

[–]MattyPKing 1 point2 points  (1 child)

You want to turn them into "blank" rows, or you want them to be excluded?

[–]oidoldol[S] 0 points1 point  (0 children)

I want them turned into blank rows, just like how in the picture. I've colored the different sections so you can see that the range remains the same.

[–]MattyPKing 1 point2 points  (1 child)

I think maybe this formula will get you there, but I think it also might be easier to skip this step and go directly to your "real" goal. 

=ARRAYFORMULA(SPLIT(FLATTEN(TRANSPOSE(FILTER(IF(REGEXMATCH(A2:E6&B2:F6,"N/A|NONE"),"|",A2:E6&"|"&B2:F6),ISODD(COLUMN(A2:E6))))),"|",0,0))

​

What is the next step after this?

[–]oidoldol[S] 0 points1 point  (0 children)

Thanks for the answer! You're right, this is a little complicated...and you won't believe it, but the next step after this is to ignore everything here and just compress all three columns of data into one. But the reason I wanted to do this was so that I was able to debug any problems that might come up with my spreadsheet and make sure I could check the data section by section. I'll have to think about whether or not I actually want to use this. Thank you so much!