Excel formula to return all non-blank values

[mwlightfoot]

Hello,

I am trying to figure out an excel formula to help me return all non-
blank values from one column into another. I'd like it to look like
the below example. All values from column A listed sequentially in
Column B with no blank spaces.

A B
1 TEST1 TEST1
2 TEST2
3 TEST2 TEST
4 BTEST
5 NOSE
6 TEST
7
8 BTEST
9
10 NOSE

Additional information:
The information in column A is text and is referenced from another
sheet and will change from time to time. The results in Column B
should also update.
I've tried multiple IF statements but that just gets messy very
quickly. Plus, it still didn't do what I needed it to.

I would appreciate any and all help you can give!!! Thank you!!

Matt

 

[Jim May]

In Cell B1 enter: (this is a single line formula) it must be entered
simultaneouly
with the Control Key + Shift + Enter key.


=IF(ROW()-ROW(NoBlanksRange)+1>ROWS(BlanksRange)-COUNTBLANK(BlanksRange),"",INDIRECT(ADDRESS(SMALL((IF(BlanksRange<>"",ROW(BlanksRange),ROW()+ROWS(BlanksRange))),ROW()-ROW(NoBlanksRange)+1),COLUMN(BlanksRange),4)))

 

[mwlightfoot]

Thank you very much! It worked great! I just had to make the first
part =IF(ROW()-ROW(BlanksRange)... instead of NoBlanksRange. Thanks
again!!

Matt