if blank

  • Thread starter Thread starter Dave
  • Start date Start date
D

Dave

Hi

I have four columns of data BCDE I want to combine into column A. I want to
say if column B is blank then show column C then if column C is blank then
show column D. It is important that it works in this order as thed ata refers
to dates i.e. last week, last month, last year, over a year.

Can anyone help?

Thanks
 
Do you just want to display the first non-blank entry and ignore all others?

The basic formula for displaying all of them would be (assuming on row 2)
=B2 & " " & C2 & " " & D2 & " " & E2
If you want to just display the first one of the four that's non-blank, post
back.
 
Hello.

This checks B, C, D then E and returns the first value, or if nothing is
found it returns nothing. (Enter into cell A1)
=IF(B2="",IF(C2="",IF(D2="",IF(E2="","",E2),D2),C2),B2)
 
Perhaps something like this:
=IF(ISBLANK(B2),IF(ISBLANK(C2),IF(ISBLANK(D2),E2,D2),C2),B2)
 
Try the below in Row1 cell A1 and copy down. Please note that this is an
array formula. Within the cell in edit mode (F2) paste this formula and press
Ctrl+Shift+Enter to apply this formula. If successful in 'Formula Bar' you
can notice the curly braces at both ends like "{=<formula>}"

=INDEX(1:1,MIN(IF(B1:E1<>"",COLUMN(B1:E1))))

If this post helps click Yes
 
If only one cell of the four have something in them, you could use a formula
like:

=b1&c1&d1&e1
or
=b1+c1+d1+e1
(if the values are numeric)

If you could have nothing in B, but something in the remaining 3 and you want
the value in column C:

=index(b1:e1,match(true,(b1:e1<>""),0))
This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

If you could have all 4 cells empty, this formula will result in an error. You
could modify it to:

=if(counta(b1:e1)=0,"",index(b1:e1,match(true,(b1:e1<>""),0)))
(still array entered)
 
Thanks Kevin. This worked perfectly.

Kevin Smith said:
Hello.

This checks B, C, D then E and returns the first value, or if nothing is
found it returns nothing. (Enter into cell A1)
=IF(B2="",IF(C2="",IF(D2="",IF(E2="","",E2),D2),C2),B2)
Click to expand...
 
Back
Top