transfer data to another column removing blanks
 

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

transfer data to another column removing blanks
 

If you dont want to keep the sort order then select the column and sort it.
OR
Select the column rangePress F5From Goto window select blanks.OKThen
right clickDeleteShift cells up.


If this post helps click Yes
---------------
Jacob Skaria


"sam" wrote:

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

transfer data to another column removing blanks
 

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" wrote:

If you dont want to keep the sort order then select the column and sort it.
OR
Select the column rangePress F5From Goto window select blanks.OKThen
right clickDeleteShift cells up.


If this post helps click Yes
---------------
Jacob Skaria


"sam" wrote:

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

transfer data to another column removing blanks
 

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

If this post helps click Yes
---------------
Jacob Skaria


"sam" wrote:

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" wrote:

If you dont want to keep the sort order then select the column and sort it.
OR
Select the column rangePress F5From Goto window select blanks.OKThen
right clickDeleteShift cells up.


If this post helps click Yes
---------------
Jacob Skaria


"sam" wrote:

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

transfer data to another column removing blanks
 

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

"Jacob Skaria" wrote:

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

If this post helps click Yes
---------------
Jacob Skaria


"sam" wrote:

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" wrote:

If you dont want to keep the sort order then select the column and sort it.
OR
Select the column rangePress F5From Goto window select blanks.OKThen
right clickDeleteShift cells up.


If this post helps click Yes
---------------
Jacob Skaria


"sam" wrote:

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

transfer data to another column removing blanks
 

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

transfer data to another column removing blanks
 

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


"Joerg Mochikun" wrote:

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

transfer data to another column removing blanks
 

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,M IN(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" wrote:

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

"Jacob Skaria" wrote:

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

If this post helps click Yes
---------------
Jacob Skaria


"sam" wrote:

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" wrote:

If you dont want to keep the sort order then select the column and sort it.
OR
Select the column rangePress F5From Goto window select blanks.OKThen
right clickDeleteShift cells up.


If this post helps click Yes
---------------
Jacob Skaria


"sam" wrote:

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

transfer data to another column removing blanks
 

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

If this post helps click Yes
---------------
Jacob Skaria


"sam" wrote:

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

"Jacob Skaria" wrote:

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

If this post helps click Yes
---------------
Jacob Skaria


"sam" wrote:

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" wrote:

If you dont want to keep the sort order then select the column and sort it.
OR
Select the column rangePress F5From Goto window select blanks.OKThen
right clickDeleteShift cells up.


If this post helps click Yes
---------------
Jacob Skaria


"sam" wrote:

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