Finding a value and enter that value in another column 1

[mflower]

This is a question for a Excel document. Would anyone know how to find a specific value in column A and when found, enter that value in another column B?
For example, A1's value is 1,2,3,4 and I would like B1's value to find any 1 and put in a value of 1 and 0 if no value of 1 is found. Problem is in Column A, the sequence of the values will not always be 1,2,3,4, but can be 2,4, or 1,4 or 2,3,4..anyone can help? So, B1 will look for any 1 and C1 will look for any value of 2 in A1 and D1 will look for any 3 in A1 and E1 will look for any 4 in A1.

LOOKUP function might not work because the numbers in column A are not in sequence.

 

[DaleWatson123321]

Hi mflower,

Thanks for a "well described" situation. ...Not everyone makes their description precise enough and the "guessing" about what they meant becomes the BIGGEST part of the challenge, instead of the actual problem itself.

The following formulas work for your situation:

Data in cell A1: 1,2,3,4
Data in cell A2: 2,4
Data in cell A3: 1,4
Data in cell A4: 2,3,4

Formula in cell B1: =IF(IF(ISERROR(FIND("1",$A1,1)),0,FIND("1",$A1,1))>0,1,0)
Formula in cell C1: =IF(IF(ISERROR(FIND("2",$A1,1)),0,FIND("2",$A1,1))>0,1,0)
Formula in cell D1: =IF(IF(ISERROR(FIND("3",$A1,1)),0,FIND("3",$A1,1))>0,1,0)
Formula in cell E1: =IF(IF(ISERROR(FIND("4",$A1,1)),0,FIND("4",$A1,1))>0,1,0)

Copy the formulas from B1:E1 to all the rows for which you have data in Column "A". This will provide the "0"s and "1"s you requested.

I trust this is what you required. Please advise.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[DaleWatson123321]

To "mflower",

In checking this posting, I noticed that you DON'T have it "MARKED".

Also, because you haven't responded, I wonder IF you indeed received the response I posted ? ? ?

Please advise.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[bkpchs237]

mflower,

I had a similar problem not too long ago. I agree that DaleWatson123321's solution will work if you only have single digits. By that I mean that if you place just the digits 0 thru 9 within your possible values set it will find your value accordingly. However, you should be aware that if you place the following sequence: 3,4,5,12 in a value set, it will also find a "1" and report it found even though the actual value is a "12." But as your question did not necessarily rule out double digit (or beyond) numbers I offer a more elaborate solution that covers that possibility. It will report if only a value of 1 is found. It takes into account that there are only four possibilities for finding a one, they are:
1 ,1 1, ,1,

I used row 1 as a header row and my values started in cell A2, A3, ... so my formulas take this into account, you can adjust the cells as needed.

Formula in cell B2: =IF(OR($A2=1,LEFT($A2,2)="1,",RIGHT($A2,2)=",1&quot,1,IF(ISERROR(FIND(",1,",$A2,1)),0,1))
Formula in cell C2: =IF(OR($A2=2,LEFT($A2,2)="2,",RIGHT($A2,2)=",2&quot,1,IF(ISERROR(FIND(",2,",$A2,1)),0,1))
Formula in cell D2: =IF(OR($A2=3,LEFT($A2,2)="3,",RIGHT($A2,2)=",3&quot,1,IF(ISERROR(FIND(",3,",$A2,1)),0,1))
Formula in cell E2: =IF(OR($A2=4,LEFT($A2,2)="4,",RIGHT($A2,2)=",4&quot,1,IF(ISERROR(FIND(",4,",$A2,1)),0,1))

Hope this helps.

 

[DaleWatson123321]

Hi bkpchs237,

Thanks for the "enhanced" version.

Hopefully "mflower" will get to notice these responses, but if not, they will hopefully serve as good examples of other "Tek-Tippers".

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca