Rephrased - Remove blank cells - no sorting, copy or pasting
 

Is there a "formula" that I can put into RowB that will remove the blank
cells from RowA without sorting or manually copying and pasting?
I posted this earlier but didn't get an anwer if there is a formula or not.

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

Rephrased - Remove blank cells - no sorting, copy or pasting
 

A simple non-array play ..
In B1: =IF(A1="","",ROW())
In C1: =IF(ROW()COUNT(B:B),"",INDEX(A:A,SMALL(B:B,ROW()) ))
Copy B1:C1 down to cover the max expected extent of data in col A.
Minimize/hide away col B. Col C will return the results that you seek, all
neatly bunched at the top.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Luke" wrote:
Is there a "formula" that I can put into RowB that will remove the blank
cells from RowA without sorting or manually copying and pasting?
I posted this earlier but didn't get an anwer if there is a formula or not.

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

Rephrased - Remove blank cells - no sorting, copy or pasting
 

Very much Appreciated!!!! I can't tell you how happy I am right now :)
Thank you again Max, you are most excellent!
Luke

"Max" wrote:

A simple non-array play ..
In B1: =IF(A1="","",ROW())
In C1: =IF(ROW()COUNT(B:B),"",INDEX(A:A,SMALL(B:B,ROW()) ))
Copy B1:C1 down to cover the max expected extent of data in col A.
Minimize/hide away col B. Col C will return the results that you seek, all
neatly bunched at the top.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Luke" wrote:
Is there a "formula" that I can put into RowB that will remove the blank
cells from RowA without sorting or manually copying and pasting?
I posted this earlier but didn't get an anwer if there is a formula or not.

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

Rephrased - Remove blank cells - no sorting, copy or pasting
 

Welcome, glad it worked out ok for you.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Luke" wrote in message
...
Very much Appreciated!!!! I can't tell you how happy I am right now :)
Thank you again Max, you are most excellent!
Luke

Rephrased - Remove blank cells - no sorting, copy or pasting
 

Max,
How would I include other sheets in these formula?
I have another sheet that would essentially take the place of RowA.
I Should have said that in the first place but I thought I could figure it
out.
Thanks Max
Luke
Example: Sheet1
A
1 8
2 6
3
4 2
5 5
6 6
7
8
9 4
Sheet2:
B
1 8
2 6
3 2
4 5
5 6
6 4
7
8
9

"Max" wrote:

Welcome, glad it worked out ok for you.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Luke" wrote in message
...
Very much Appreciated!!!! I can't tell you how happy I am right now :)
Thank you again Max, you are most excellent!
Luke

Rephrased - Remove blank cells - no sorting, copy or pasting
 

Max,
I also need to know how to keep from using "full row reference" in the
formula as it slows me way down on calculating.
Regards
Luke

"Max" wrote:

Welcome, glad it worked out ok for you.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Luke" wrote in message
...
Very much Appreciated!!!! I can't tell you how happy I am right now :)
Thank you again Max, you are most excellent!
Luke

Rephrased - Remove blank cells - no sorting, copy or pasting
 

How would I include other sheets in these formula?

If your source data is in Sheet1, in A1 down
Then in Sheet2,
In A1: =IF(Sheet1!A1="","",ROW())
In B1:
=IF(ROW()COUNT(A:A),"",INDEX(Sheet1!A:A,SMALL(A:A ,ROW())))
Copy A1:B1 down to cover the max expected extent of data in Sheet1's col A,
eg down to B200. Minimize/hide away col A. Col B will return the results that
you seek, all neatly bunched at the top.

I also need to know how to keep from using "full row reference"
in the formula as it slows me way down on calculating.

I don't think there's a material performance difference once you limit the
copy down to the smallest range large enough to cover the max expected extent
of source data (eg to B200 in the above). It's always neater to use entire
col refs.

Anyway, if you want to use fixed ranges ...
If source data in Sheet1 is expected within A1:A200 only
Then you could use instead in Sheet2's B1:
=IF(ROW()COUNT(A$1:A$200),"",INDEX(Sheet1!A$1:A$2 00,SMALL(A$1:A$200,ROW())))
(no change to A1's formula)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Rephrased - Remove blank cells - no sorting, copy or pasting
 

I love it! I do beleive I hear the cats meow when I plugged that in :)
Max, You Rock to the Max!
Most people wouldn't buy the cow when the milk is free but right now I wish
I could pay you.
Your are one of the best in my book,
Best regards to you,
Luke

"Max" wrote:

How would I include other sheets in these formula?

If your source data is in Sheet1, in A1 down
Then in Sheet2,
In A1: =IF(Sheet1!A1="","",ROW())
In B1:
=IF(ROW()COUNT(A:A),"",INDEX(Sheet1!A:A,SMALL(A:A ,ROW())))
Copy A1:B1 down to cover the max expected extent of data in Sheet1's col A,
eg down to B200. Minimize/hide away col A. Col B will return the results that
you seek, all neatly bunched at the top.

I also need to know how to keep from using "full row reference"
in the formula as it slows me way down on calculating.

I don't think there's a material performance difference once you limit the
copy down to the smallest range large enough to cover the max expected extent
of source data (eg to B200 in the above). It's always neater to use entire
col refs.

Anyway, if you want to use fixed ranges ...
If source data in Sheet1 is expected within A1:A200 only
Then you could use instead in Sheet2's B1:
=IF(ROW()COUNT(A$1:A$200),"",INDEX(Sheet1!A$1:A$2 00,SMALL(A$1:A$200,ROW())))
(no change to A1's formula)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Rephrased - Remove blank cells - no sorting, copy or pasting
 

Welcome, Luke. Glad to hear the sounds of satisfaction!
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Luke" wrote in message
...
I love it! I do believe I hear the cats meow when I plugged that in :)
Max, You Rock to the Max!
Most people wouldn't buy the cow when the milk is free but right now I
wish
I could pay you.
Your are one of the best in my book,
Best regards to you,
Luke