An Array To Sort Blank Cell

[travelersway]

I'd like to create an array to place values in column A into Column B
and remove blank cell from column A.

Example:data in column "A" has blank cell at A2, I'd like to have
column "B" show the values and elimate the blank cell.

A............B
4............4
blank.......5
5............6
6

Your assistance is appreciated.

Travelersway

 

[Biff]

Hi!

Enter this formula as an array using the key combo of CTRL,SHIFT,ENTER in B1
and copy down until you get blanks:

=IF(ROWS($1:1)<=COUNTA(A$1:A$4),INDEX(A$1:A$4,SMALL(IF(A$1:A$4<>"",ROW(A$1:A$4)-ROW(A$1)+1),ROWS($1:1))),"")

Biff

"travelersway" <[email protected]>
wrote in message
news:[email protected]...

 

[travelersway]

Biff,

Thanks. the data moves to the column that I want, but all the empt
cellsare not removed.my actual perameters are

C63:C270 column for holding data
K168:K374 column for sorted data

Does this make a difference? The data in this sort are numbers. I woul
like to sort two additional columns as well in seperate sorts for:

DATES: 1/12

TEXT: name

Any help on those?

Thank you again.

Travelerswa

 

[Peo Sjoblom]

Define an empty cell? Is it totally empty or are there spaces in them?
Did you mean to remove empty cells from the A column itself? No formula can
do that!
Biff's formula extract and sort only non empty cells but it has no affect on
column A itself.
You can do that manually by select column A, press F5, click special, select
blanks then delete the cells

--

Regards,

Peo Sjoblom

Northwest Excel Solutions

www.nwexcelsolutions.com

(remove ^^ from email address)

Portland, Oregon




"travelersway" <[email protected]>
wrote in message
news:[email protected]...

 

[Biff]

See Peo's reply. He summed it up well.

Biff

"travelersway" <[email protected]>
wrote in message
news:[email protected]...

 

[travelersway]

Thank you for your help.

Travelersway