remove blank cells - no sorting

[Luke]

Is there a way to have contents of row A placed in row B without the blank
cells and without sorting?

Example:

A B
1 8 8
2 6 6
3 2
4 2 5
5 5 6
6 6 4
7
8
9 4

Thank you for your help
Luke

 

[Teethless mama]

Copy data from Column A and Paste it to Column B > hit F5 > Special > Blanks

 

[Ron Coderre]

If I understand you correctly, you want to copy the Col_A values
and paste them over the Col_B values...but you don't want the
blanks in Col_A to write over values in Col_B.

If that's correct, try this:

Using your example
Copy the Col_A values (A1:A9)
Select cell B1

From the Excel Main Menu:
<edit><paste special>
....Check: Values
....Check: Skip blanks
Click [OK]
Press the [ESC] key to end cut/copy mode.

Does that help?...If no, <edit><undo>
Post back if you have more questions.
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

 

[Luke]

Yes that helps. However I was hoping for a formula to place in row B then
fill down because there are so much data and as the new data arives it gets
cumbersome to manually copy.., then pasting also messes with fomulas in other
cells that read row B.
Does that make sense?

If I understand you correctly, you want to copy the Col_A values
and paste them over the Col_B values...but you don't want the
blanks in Col_A to write over values in Col_B.

If that's correct, try this:

Using your example
Copy the Col_A values (A1:A9)
Select cell B1

From the Excel Main Menu:
<edit><paste special>
...Check: Values
...Check: Skip blanks
Click [OK]
Press the [ESC] key to end cut/copy mode.

Does that help?...If no, <edit><undo>
Post back if you have more questions.
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

Is there a way to have contents of row A placed in row B without the blank
cells and without sorting?

Example:

A B
1 8 8
2 6 6
3 2
4 2 5
5 5 6
6 6 4
7
8
9 4

Thank you for your help
Luke

 

[Ron Coderre]

Sorry, Luke...I misunderstood your request.

See if this helps:

Enter this ARRAY FORMULA, committed with CTRL+SHIFT+ENTER,
instead of just ENTER, in cell....
B1: =IF(COUNT($A$1:$A$10)>=ROWS($1:1),
INDEX($A$1:$A$10,SMALL(IF($A$1:$A$10<>"",
ROW($A$1:$A$10)),ROWS($1:1))),"")

Copy B1 and paste into B2 through B10

That formula lists each item from Col_A, in the order encountered
and without blanks.

Adjust range references to suit your actual situation.
Note: If Col_A might contain text that you want to list,
use COUNTA, instead of COUNT.

Does that help?
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

Yes that helps. However I was hoping for a formula to place in row B then
fill down because there are so much data and as the new data arives it
gets
cumbersome to manually copy.., then pasting also messes with fomulas in
other
cells that read row B.
Does that make sense?

If I understand you correctly, you want to copy the Col_A values
and paste them over the Col_B values...but you don't want the
blanks in Col_A to write over values in Col_B.

If that's correct, try this:

Using your example
Copy the Col_A values (A1:A9)
Select cell B1

From the Excel Main Menu:
<edit><paste special>
...Check: Values
...Check: Skip blanks
Click [OK]
Press the [ESC] key to end cut/copy mode.

Does that help?...If no, <edit><undo>
Post back if you have more questions.
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

Is there a way to have contents of row A placed in row B without the
blank
cells and without sorting?

Example:

A B
1 8 8
2 6 6
3 2
4 2 5
5 5 6
6 6 4
7
8
9 4

Thank you for your help
Luke