Multiple Column IF Condition

[sbrotz]

I am working with a large worksheet consisting of more than 30,000 rows. I have a need to populate "column A" of each row based on the value of six non-contiguous columns.
If all six columns ( Columns Q, T, W, Z, AC, AND AF) are valued with N/A or are Blank, then
"column A" for the row should have the word ORPHAN.

I know how to value base on one column << =IF(OR(Q415="N/A",Q415=""),"ORPHAN","") >>, but not multiples.

Thanks for the look!

 

[HalfAce]

Hello sbrotz, welcome to the board.
If I understand - (that being if these cells all have N/A, or they are all blank you
want to return "ORPHAN") then something like this should work.

=IF(OR(AND(Q415="N/A",T415="N/A",W415="N/A",Z415="N/A",AC415="N/A",AF415="N/A"),AND(Q415="",T415="",W415="",Z415="",AC415="",AF415="")),"ORPHAN","")

Or do you need to display 'ORPHAN' if all of those cells display either N/A, or blank?

 

[PaddyD]

longhand:

=if(and(or(q415="NA",q415=""),or(t415="NA",t415=""),....),"ORPHAN","")

...yuou can fill in the blanks!

 

[Scott Huish]

If they can be any combination of N/A or blank, then perhaps:

=IF(LEN(SUBSTITUTE(Q415&T415&W415&Z415&AC415&AF415,"N/A","")),"","ORPHAN")

 

[PaddyD]

=IF(LEN(SUBSTITUTE(Q415&T415&W415&Z415&AC415&AF415,"N/A","")),"","ORPHAN")


Nice approach.

 

[sbrotz]

Half Ace - thanks for the quick reply. It is the second situation that needs' to display ORPHAN

'ORPHAN' if all referenced cells display either N/A, or blank.

 

[HalfAce]

You're welcome.
It looks like PaddyD & HOTPEPPER both have you taken care of.

(And yeah, I like HOTPEPPER's approach too.)

 

[Domenic]

If they can be any combination of N/A or blank, then perhaps:

=IF(LEN(SUBSTITUTE(Q415&T415&W415&Z415&AC415&AF415,"N/A","")),"","ORPHAN")

I like it too. Nice one!

 

[Scott Huish]

Thanks!