I have a column in an excel file which contains 1 to 20 numbers, each separated by a blank (I’ll call this the datacolumn) .
I would like to create some new columns which would have a 1 if a certain number exists in the datacolumn.
For example a new column called yes3 which would have a value of 1 if the number 3 is in the datacolumn, 0 if it is not.
or a new column called yes17 which would have a value of 1 if the number 17 is in the datacolumn, 0 if it is not.
Is this possible in excel using a formula (if or iif ?) in each of the new columns? If not excel then maybe access?
Datacolumn yes3 yes17
1 6 7 8 12 17 20 0 1
2 0 0
1 2 3 4 5 6 1 0