inserting texts in cell based on conditions

[Nic]

Hi,

I would very much appreciate if someone could help me
solving a problem, illustrated by the following example:

Column A B C
1 1 "LB"
2 1 "DK"
3
4 1 1 "LB/DK"


If there's a 1 in column A, the corresponding cell in
column C should get the text "LB" inserted into it.

If there's a 1 in column C, the corresponding cell in
column C should get the text "DK" inserted into it.

If both column A and B have ones in them, the
corresponding cell should get the text "LB/DK" inserted
into it.

Haven't been able to figure this out and would appreciate
any help and suggestions.

Cheers
Nic

 

[JohnI]

Nic,

Try =IF(A2=1,IF(B2=1,"LB/DK","LB"),IF(B2=1,"DK",""))

regards,

JohnI

 

[Max]

One way:

Put in
C1:=IF(AND(A1=1,B1=1),"LB/DK",IF(A1=1,"LB",IF(B1=1,"DK","")))
Copy down col C

 

[Mike]

=IF(A1,"LB","")&IF(A1+B1=2,"/","")&IF(B1,"DK","")

 

[Max]

Assuming it is acceptable to have zeros to replace blank cells, a
"binary" like set-up using VLOOKUP might be one possible way to
go.

This avoids having "indecipherable and hard-to-maintain" nested
IF()'s and also averts the limit faced for nested IF()s (albeit
there are ways to get around this limit).

Assuming the input cols are cols A to D

Set-up a vlookup table (sample below) in a 2 col range,
say in H1:I12, and name this range: List

(yes, a fair amount of one-time effort is required to set this
up. And you got to cover all the "binary" like permutations of
1's and 0's depending on the number of input cols involved. The
sample set below *doesn't* cover all permutations.)

0_0_0_0 LB
0_0_0_1 DK
0_0_1_0 LB/DK
0_0_1_1 AB
0_1_1_1 CD
1_1_1_1 EF
1_0_0_0 AB/CD
1_0_0_1 AB/EF
1_0_1_1 GH
1_1_0_1 IJ
1_1_1_0 GH/IJ
1_1_0_0 KL

Put in E1:=VLOOKUP(A1&"_"&B1&"_"&C1&"_"&D1,List,2,FALSE)

If A11 contains 1,0,0,1 then E1 will return AB/EF

Copy down col E