How to combine two different columns

Question

I have two different columns with emails and I'd like to display all the emails in a separate column in the spreadsheet. Both the columns can be extended further so I can't select all cells statically. I tried doing ={(J2:J); (I2:I)} but it doesn't work.

Answer 1

You can use the FLATTEN Function to flatten all the values from one or more ranges into a single column. Empty values are not skipped.

=flatten(I2:J)

Answer 2

I tried doing ={(J2:J); (I2:I)} but it doesn't work.

It isn't working because combining the two columns creates a column that is slightly less than double the height of your current spreadsheet.

You could reduce the height of your ranges, or remove any blank rows making sure the height of the spreadsheet is enough to accommodate all the rows containing values.

This last is the approach you appear to have taken using FILTER ^* however TOCOL will remove the blank values with less repetition:

=TOCOL({J2:J;I2:I},1)

It can also be combined with @pjmg's FLATTEN approach

=TOCOL(FLATTEN(I2:J),1)

FILTER  FLATTEN  LET  TOCOL

---

 * OP's comment:

Using FILTER would require something like:

  =FILTER({J2:J;I2:I},{J2:J;I2:I}<>"")       // Array using brackets {} 
  
  =FILTER(FLATTEN(I2:J),FLATTEN(I2:J)<>"")   // Flatten a range
  
  =LET(f,FLATTEN(I2:J),FILTER(f, f<>"")      // Add LET