Formula to delete blank cells across multiple columns?

S

SamFunMail

I am looking to delete Blank cells down multiple columns with a single
function or formula. I know the ISBLANK function exists.

But can I delete a cell if it is Blank using a formula ?
In the example below, if row denotes Ranks:

RANK A B C
1 x y
2 r t
3 a b
4 n

I want to rank across the various columns A B C by deleting blank cells and
moving the nonblank cells up

A B C
1 x r y
2 a b t
3 n
4
 
M

Max

Perhaps this may suffice ..

Select the columns (select the col headers for cols B to D)
Press F5 > Special > Check "Blanks" > OK
Right click on the selection > Delete
Check "Shift cells up" > OK
 
M

Max

Formulas can only return evaluated results, they cannot delete cells, blank
or otherwise.

But if you're looking for a formulas approach to auto-output in say, another
sheet the desired results in a visual sense, i.e. move all non-blank cells
up their columns , one non-array way:

Assume the source table is in Sheet1,
cols A to D, data from row2 down

Using empty cols to the right,
Put in F2: =IF(A2="","",ROW())
Copy F2 across as many cols as the source table, i.e. to I2, then fill down
to say, F100, to cover the max expected data in the table

In a new Sheet2:

With the same col headers pasted over in A1:D1,

Put in A2:
=IF(ISERROR(SMALL(Sheet1!F:F,ROWS($A$1:A1))),"",INDEX(Sheet1!A:A,MATCH(SMALL
(Sheet1!F:F,ROWS($A$1:A1)),Sheet1!F:F,0)))

Copy A2 across to D2, fill down to D100
(cover the same range size as per cols F to I in Sheet1)

Sheet2 will auto-output the results that you want,
moving all non-blank cells in Sheet1 up, column-wise.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top