Resort Column from one with blanks

[Guest]

I used to know how to do this but forgot the formula....

ex:

A | B
--------
4 | 4
C |
6 | C
7 | 6
|
| 7


Any help would be greatly appreciated.

 

[Guest]

One way using non-array formulas (& simple entire col refs) ..

Assuming source data in B2 down,

Put in C2:
=IF(B2="","",ROW())
(Leave C1 blank)

Put in D2:
=IF(ROW(A1)>COUNT(C:C),"",INDEX(B:B,MATCH(SMALL(C:C,ROW(A1)),C:C,0)))

Then just select C22, copy down to cover the max expected extent of source
data in col B, say down to B100? Hide away col C. Col D will automatically
"pull up" the data in the desired manner to neatly bunch at the top. Adapt to
suit. If you want it extracted in col A, just place the 2nd formula in A2
instead, then copy down cols A and C to the same extent.

 

[Guest]

One way using non-array formulas (& simple entire col refs) ..

Assuming source data in B2 down,

Put in C2:
=IF(B2="","",ROW())
(Leave C1 blank)

Put in D2:
=IF(ROW(A1)>COUNT(C:C),"",INDEX(B:B,MATCH(SMALL(C:C,ROW(A1)),C:C,0)))

Then just select C22, copy down to cover the max expected extent of source
data in col B, say down to B100? Hide away col C. Col D will automatically
"pull up" the data in the desired manner to neatly bunch at the top. Adapt to
suit. If you want it extracted in col A, just place the 2nd formula in A2
instead, then copy down cols A and C to the same extent.