Convert Range of numbers to Either 1,0

**Descent73** · 11-21-2012, 01:49 PM

ID Q3.1_a Q3.1_b Q3.1_c Q3.1_d Q3.1_e Q3.1_f Q3.1_g
1 2 21
2 9 11 13 21
3 1 11 28
4 4 5
5 4 7 11 28
6 2 6 22

I would like to convert the above excel data to 1 or 0's.

There in 5nd row (ID=4) I will have 1 in the 4th and 5th cell, all others will have a Zero.

V1 V2 V3 V4 V5 V6 V7 V8 V9
0 0 0 1 1 0 0 0 0

I could you an IF function, but then only way I know will have to look at each cell separately, if (Q3.1_a=1 OR Q3.1_b = 1. etc)
Where I would rather lookup the range Q3.1_a:Q3.1_g

Thanks for the help.
Brendan

**Norie** · 11-21-2012, 01:50 PM

Brendan

What about the rest of the rows?

**Descent73** · 11-21-2012, 01:55 PM

If you asking about the 6 rows in my example. Here goes.

Row 2 - ID 1. 2nd and 21th cell will have a 1, the rest will all have zero's
Row 3 - ID 2. The 9th, 11th, 13th and 21th cell will have a 1, the rest will all have zero's
etc...

I know that I can have a range between 1 and 71 in those cells so I would need 71 new cells in the new data.

Does this explain better ?

**Norie** · 11-21-2012, 02:00 PM

Brendan

Are you just setting all the cells with a value not equal to 0 to 1?

**FDibbins** · 11-21-2012, 02:07 PM

To best describe or illustrate your problem you would be better off attaching a dummy workbook. The workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

**Descent73** · 11-21-2012, 03:20 PM

Dummy Data.xlsx

I've attached the file, I hope it helps with my explanation.

I don't want to just check where there are 1's or 0, but if I find a number put a 1 in it's location.

Thanks,
Brendan