software

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 quot;Aquot; has blank cell at A2, I'd like to have
column quot;Bquot; 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: www.excelforum.com/member.php...oamp;userid=17623
View this thread: www.excelforum.com/showthread...hreadid=518077Hi!

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)lt;=COUNTA(A$1:A$4),INDEX(A$1:A$4,SMAL L(IF(A$1:A$4lt;gt;quot;quot;,ROW(A$1:A$4)-ROW(A$1) 1),ROWS($1:1))),quot;quot;)

Biff

quot;travelerswayquot; gt;
wrote in message
news:travelersway.240pmo_1141261810.6701@excelforu m-nospam.com...
gt;
gt; I'd like to create an array to place values in column A into Column B
gt; and remove blank cell from column A.
gt;
gt; Example:data in column quot;Aquot; has blank cell at A2, I'd like to have
gt; column quot;Bquot; show the values and elimate the blank cell.
gt;
gt; A............B
gt; 4............4
gt; blank.......5
gt; 5............6
gt; 6
gt;
gt; Your assistance is appreciated.
gt;
gt; Travelersway
gt;
gt;
gt; --
gt; travelersway
gt; ------------------------------------------------------------------------
gt; travelersway's Profile:
gt; www.excelforum.com/member.php...oamp;userid=17623
gt; View this thread: www.excelforum.com/showthread...hreadid=518077
gt;

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: www.excelforum.com/member.php...oamp;userid=17623
View this thread: www.excelforum.com/showthread...hreadid=518077Define 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

quot;travelerswayquot; gt;
wrote in message
...
gt;
gt; Biff,
gt;
gt; Thanks. the data moves to the column that I want, but all the empty
gt; cellsare not removed.my actual perameters are
gt;
gt; C63:C270 column for holding data
gt; K168:K374 column for sorted data
gt;
gt; Does this make a difference? The data in this sort are numbers. I would
gt; like to sort two additional columns as well in seperate sorts for:
gt;
gt; DATES: 1/12
gt;
gt; TEXT: name
gt;
gt; Any help on those?
gt;
gt; Thank you again.
gt;
gt; Travelersway
gt;
gt;
gt; --
gt; travelersway
gt; ------------------------------------------------------------------------
gt; travelersway's Profile:
gt; www.excelforum.com/member.php...oamp;userid=17623
gt; View this thread: www.excelforum.com/showthread...hreadid=518077
gt;See Peo's reply. He summed it up well.

Biff

quot;travelerswayquot; gt;
wrote in message
...
gt;
gt; Biff,
gt;
gt; Thanks. the data moves to the column that I want, but all the empty
gt; cellsare not removed.my actual perameters are
gt;
gt; C63:C270 column for holding data
gt; K168:K374 column for sorted data
gt;
gt; Does this make a difference? The data in this sort are numbers. I would
gt; like to sort two additional columns as well in seperate sorts for:
gt;
gt; DATES: 1/12
gt;
gt; TEXT: name
gt;
gt; Any help on those?
gt;
gt; Thank you again.
gt;
gt; Travelersway
gt;
gt;
gt; --
gt; travelersway
gt; ------------------------------------------------------------------------
gt; travelersway's Profile:
gt; www.excelforum.com/member.php...oamp;userid=17623
gt; View this thread: www.excelforum.com/showthread...hreadid=518077
gt;

Thank you for your help.

Travelersway--
travelersway
------------------------------------------------------------------------
travelersway's Profile: www.excelforum.com/member.php...oamp;userid=17623
View this thread: www.excelforum.com/showthread...hreadid=518077