Excel formula to return all non-blank values
 

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

Excel formula to return all non-blank values
 

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)+1ROWS(BlanksRange)-COUNTBLANK(BlanksRange),"",INDIRECT(ADDRESS(SMALL( (IF(BlanksRange<"",ROW(BlanksRange),ROW()+ROWS(Bl anksRange))),ROW()-ROW(NoBlanksRange)+1),COLUMN(BlanksRange),4)))


" wrote:

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

Excel formula to return all non-blank values
 

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

On Feb 14, 4:01*pm, Jim May wrote:
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)+1ROWS(BlanksRange)-COUNTBLANK(BlanksRange),""*,INDIRECT(ADDRESS(SMALL ((IF(BlanksRange<"",ROW(BlanksRange),ROW()+ROWS(B la*nksRange))),ROW()-ROW(NoBlanksRange)+1),COLUMN(BlanksRange),4)))



" wrote:
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- Hide quoted text -

- Show quoted text -