Formula to delete blank cells across multiple columns?
 

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

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
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"SamFunMail" wrote in message
...


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

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))),"",IN DEX(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.
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--