Sheets: Condense a column with empty cells

Question

I have a Google Sheet with multiple columns and some of the columns have empty cells at arbitrary locations. I want to 'condense' the columns, that is, simply shift upwards all cells in the column so that the column only consists of cells with values inside. All of my Google searches provide results for removing entire blank rows/columns, but do not relate to dealing with empty cells on the row/column level.

A demo Sheet can be found here.

Answer 1

use:

=ARRAYFORMULA({A1:C1; TRANSPOSE(SPLIT(TRANSPOSE(QUERY(A2:C,,999^99)), " "))})

---

---

Props: Player0

Answer 2

Interestingly enough, collapsing the rows first and then the columns will produce a different result

Your question is best and easiest done via a script. The full code is here:

The basic steps are:

1. row collapse the grid, using:

var r = row.filter((a) => { return a && a != ''});

2. column collapsing = transpose, row collapse it, and then transpose again

There are a bunch of nitty-gritty details that you are better consulting the code. The lack of commenting there is only because I write self-describing code :-))) ,, NOT

HOW TO USE THIS

• Tools - Code Editor
• Copy and Paste the code into code.gs
• Four custom functions are now available (*)
• Example usage can be found when you do View - Show Formula

(*) You can change the names to your liking. As of now, they are:

• rowCollapse()
• colCollapse()
• rowColCollapse()
• colRowCollapse()

It does look like you only need colCollapse. I misread your question and that's why came up with 4 functions (though 2 of them as core tasks are required, as described above)

USAGE,, just like Sheet built-in functions, is case-insensitive, of course.

Answer 3

Use BYCOL to iterate across the columns one by one.

Use TOCOL to remove the blanks.

=BYCOL(A:C, LAMBDA(col, TOCOL(col, 1)))

FILTER can replace TOCOL but it's a longer formula and will error on empty columns which TOCOL won't. The errors can be handled but will make the formula even longer.

=BYCOL(A:C, LAMBDA(col, FILTER(col, LEN(col))))

# with error handling

=BYCOL(A:C, LAMBDA(col, IFERROR(FILTER(col, LEN(col)))))