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

[Luke]

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

 

[Max]

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.

 

[Luke]

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

 

[Max]

Welcome, glad it worked out ok for you.

 

[Luke]

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

 

[Luke]

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]

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$200,SMALL(A$1:A$200,ROW())))
(no change to A1's formula)

 

[Luke]

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]

Welcome, Luke. Glad to hear the sounds of satisfaction!