An Array To Sort Blank Cell
 

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


--
travelersway
------------------------------------------------------------------------
travelersway's Profile: http://www.excelforum.com/member.php...o&userid=17623
View this thread: http://www.excelforum.com/showthread...hreadid=518077

An Array To Sort Blank Cell
 

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,SMAL L(IF(A$1:A$4<"",ROW(A$1:A$4)-ROW(A$1)+1),ROWS($1:1))),"")

Biff

"travelersway"
wrote in message
news:travelersway.240pmo_1141261810.6701@excelforu m-nospam.com...

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


--
travelersway
------------------------------------------------------------------------
travelersway's Profile:
http://www.excelforum.com/member.php...o&userid=17623
View this thread: http://www.excelforum.com/showthread...hreadid=518077

An Array To Sort Blank Cell
 

Biff,

Thanks. the data moves to the column that I want, but all the empty
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 would
like to sort two additional columns as well in seperate sorts for:

DATES: 1/12

TEXT: name

Any help on those?

Thank you again.

Travelersway


--
travelersway
------------------------------------------------------------------------
travelersway's Profile: http://www.excelforum.com/member.php...o&userid=17623
View this thread: http://www.excelforum.com/showthread...hreadid=518077

An Array To Sort Blank Cell
 

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"
wrote in message
...

Biff,

Thanks. the data moves to the column that I want, but all the empty
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 would
like to sort two additional columns as well in seperate sorts for:

DATES: 1/12

TEXT: name

Any help on those?

Thank you again.

Travelersway


--
travelersway
------------------------------------------------------------------------
travelersway's Profile:
http://www.excelforum.com/member.php...o&userid=17623
View this thread: http://www.excelforum.com/showthread...hreadid=518077

An Array To Sort Blank Cell
 

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

Biff

"travelersway"
wrote in message
...

Biff,

Thanks. the data moves to the column that I want, but all the empty
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 would
like to sort two additional columns as well in seperate sorts for:

DATES: 1/12

TEXT: name

Any help on those?

Thank you again.

Travelersway


--
travelersway
------------------------------------------------------------------------
travelersway's Profile:
http://www.excelforum.com/member.php...o&userid=17623
View this thread: http://www.excelforum.com/showthread...hreadid=518077

An Array To Sort Blank Cell
 

Thank you for your help.

Travelersway


--
travelersway
------------------------------------------------------------------------
travelersway's Profile: http://www.excelforum.com/member.php...o&userid=17623
View this thread: http://www.excelforum.com/showthread...hreadid=518077