An Array To Sort Blank Cell

  • Thread starter Thread starter travelersway
  • Start date Start date
T

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
 
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]...
 
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
 
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]...
 
Back
Top