remove blanks using formula
 

I want to remove the blanks in "A" using a formula in "B"

example:

A1 - sand
A2 -
A3 - silt

Formula in "B"

B1 - sand
B2 - silt

Thankyou

remove blanks using formula
 

Assume values in ColA are actual valuesIn cell B1 enter the below formula and
copy down as required; Please note that this is an array formula. An array
formula can perform multiple calculations and then return either a single
result or multiple results. You create array formulas in the same way that
you create other formulas, except you press CTRL+SHIFT+ENTER to enter the
formula. If successful in 'Formula Bar' you can notice the curly braces at
both ends like "{=<formula}"

=IF(COUNTIF($A$1:$A$1000,"?*")<ROW(A1),"",INDEX(A$ 1:A$1000,
SMALL(IF($A$1:$A$1000<"",ROW($A$1:$A$1000)),ROW(A 1))))

--
Jacob


"Gotroots" wrote:

I want to remove the blanks in "A" using a formula in "B"

example:

A1 - sand
A2 -
A3 - silt

Formula in "B"

B1 - sand
B2 - silt

Thankyou

remove blanks using formula
 

Select the A column cell and press Cntrl+G click on Special select Blanks
Option Button and give ok. Now the blank cells of A Column will be selected.
Now do right click and select Delete and select Entire Row Option button.

In keyboard
Place the cursor in A Column Cell and press the following keyboard buttons
F5Alt+SAlt+KEnterRight ClickDAlt+R

Remember to Click Yes, if this post helps!

--------------------
(Ms-Exl-Learner)
--------------------


"Gotroots" wrote:

I want to remove the blanks in "A" using a formula in "B"

example:

A1 - sand
A2 -
A3 - silt

Formula in "B"

B1 - sand
B2 - silt

Thankyou

remove blanks using formula
 

Jacob

Your solution worked wonderfully. Just interested to know what you meant by;

Assume values in ColA are actual values

"A" are values returned by a formula and this had not any influence on your
formula working.

Thanks again

"Jacob Skaria" wrote:

Assume values in ColA are actual valuesIn cell B1 enter the below formula and
copy down as required; Please note that this is an array formula. An array
formula can perform multiple calculations and then return either a single
result or multiple results. You create array formulas in the same way that
you create other formulas, except you press CTRL+SHIFT+ENTER to enter the
formula. If successful in 'Formula Bar' you can notice the curly braces at
both ends like "{=<formula}"

=IF(COUNTIF($A$1:$A$1000,"?*")<ROW(A1),"",INDEX(A$ 1:A$1000,
SMALL(IF($A$1:$A$1000<"",ROW($A$1:$A$1000)),ROW(A 1))))

--
Jacob


"Gotroots" wrote:

I want to remove the blanks in "A" using a formula in "B"

example:

A1 - sand
A2 -
A3 - silt

Formula in "B"

B1 - sand
B2 - silt

Thankyou

remove blanks using formula
 

Oops.. Initially I created a solution whch works on actual values (not
formulas); later I changed it to consider formulas too...but forgot to retype
the text...

I used COUNTA() which was later changed to COUNTIF()

--
Jacob


"Gotroots" wrote:

Jacob

Your solution worked wonderfully. Just interested to know what you meant by;

Assume values in ColA are actual values

"A" are values returned by a formula and this had not any influence on your
formula working.

Thanks again

"Jacob Skaria" wrote:

Assume values in ColA are actual valuesIn cell B1 enter the below formula and
copy down as required; Please note that this is an array formula. An array
formula can perform multiple calculations and then return either a single
result or multiple results. You create array formulas in the same way that
you create other formulas, except you press CTRL+SHIFT+ENTER to enter the
formula. If successful in 'Formula Bar' you can notice the curly braces at
both ends like "{=<formula}"

=IF(COUNTIF($A$1:$A$1000,"?*")<ROW(A1),"",INDEX(A$ 1:A$1000,
SMALL(IF($A$1:$A$1000<"",ROW($A$1:$A$1000)),ROW(A 1))))

--
Jacob


"Gotroots" wrote:

I want to remove the blanks in "A" using a formula in "B"

example:

A1 - sand
A2 -
A3 - silt

Formula in "B"

B1 - sand
B2 - silt

Thankyou

remove blanks using formula
 

No worries


"Jacob Skaria" wrote:

Oops.. Initially I created a solution whch works on actual values (not
formulas); later I changed it to consider formulas too...but forgot to retype
the text...

I used COUNTA() which was later changed to COUNTIF()

--
Jacob


"Gotroots" wrote:

Jacob

Your solution worked wonderfully. Just interested to know what you meant by;

Assume values in ColA are actual values

"A" are values returned by a formula and this had not any influence on your
formula working.

Thanks again

"Jacob Skaria" wrote:

Assume values in ColA are actual valuesIn cell B1 enter the below formula and
copy down as required; Please note that this is an array formula. An array
formula can perform multiple calculations and then return either a single
result or multiple results. You create array formulas in the same way that
you create other formulas, except you press CTRL+SHIFT+ENTER to enter the
formula. If successful in 'Formula Bar' you can notice the curly braces at
both ends like "{=<formula}"

=IF(COUNTIF($A$1:$A$1000,"?*")<ROW(A1),"",INDEX(A$ 1:A$1000,
SMALL(IF($A$1:$A$1000<"",ROW($A$1:$A$1000)),ROW(A 1))))

--
Jacob


"Gotroots" wrote:

I want to remove the blanks in "A" using a formula in "B"

example:

A1 - sand
A2 -
A3 - silt

Formula in "B"

B1 - sand
B2 - silt

Thankyou