transfer data to another column removing blanks

[sam]

hi everyone

here is my problem and i am sure there is a simple solution. i have a 1
column of data (text) and i want to transfer it to another column but remove
the blanks. can anyone help?

cheers

sam

 

[Jacob Skaria]

If you dont want to keep the sort order then select the column and sort it.
OR
Select the column range>Press F5>From Goto window select blanks.>OK>Then
right click>Delete>Shift cells up.


If this post helps click Yes

 

[sam]

Jacob

I was more thinking of a formula that will create a new list just without
the blanks. i want it to change when i make ammendments to the primary data.

cheers

sam

 

[Jacob Skaria]

Without a reference column it would be difficult especially when you have
duplicate entries in your data

If this post helps click Yes

 

[sam]

jacob

thanks for teh quick reply. there are no duplicates just blanks. here is a
rough example of what the raw data looks like.

----A
1--car
2--
3--dog
4--cat
5--
6--house

the end result i am chasing is
----B
1--car
2--dog
3--cat
4--house

cheers

sam

 

[Joerg Mochikun]

If "blanks" means blank cells, then

1) Select text column
2) Press F5 (GoTo) => Special => Select 'Constants'
3) Press Ctrl+C (Copy)
4) Go to your destination column and Paste (Ctrl+V)

Cheers,
Joerg

 

[sam]

Joerg

thanks for the reply. here is a rough example of what the raw data looks like.

----A
1--car
2--
3--dog
4--cat
5--
6--house

the end result i am chasing is
----B
1--car
2--dog
3--cat
4--house

i do not want to have to copy and paste every time the data changes. is
there something i can do using a formula?

cheers

sam

 

[Jacob Skaria]

Hi Sam

If you dont have duplicate entries; try this..Please note that this is an
array formula. Within the cell in edit mode (F2) paste this formula and press
Ctrl+Shift+Enter to apply this formula. If successful in 'Formula Bar' you
can notice the curly braces at both ends like "{=<formula>}"

Your data in Col A..
B1
=INDEX(A1:A100,MIN(IF(A1:A100="","",ROW(A1:A100))))

B2 (all in one line)
=IF(COUNTA($A$1:$A$100)>=ROW(),INDEX($A$1:$A$100,MIN(IF(INDIRECT("A" &
MATCH(B1,$A$1:$A$100,0)+1 &":A100")="","",ROW(INDIRECT("A" &
MATCH(B1,$A$1:$A$100,0)+1 & ":A100"))))),"")


Try and feedback

If this post helps click Yes

 

[Jacob Skaria]

Sam; forgot to mention that the formula in B2 is to be copied down....

If this post helps click Yes