Converting Data into 0's and 1's = Excel 2003

[Descent73]

Hi,

Sorry if this has been posted before, first time poster.

I want to convert data from one way to another. Below are the examples:


ID Variable_A Variable_B Variable_C
1001 1
1002 2 5
1003 1 2
1004 2 3
1005 3 5 4
1006 1 2
1007 4
1008 5
1009 2 4

INTO:


ID Var_1 Var_2 Var_3 Var_4 Var_5
1001 1 0 0 0 0
1002 0 1 0 0 1
1003 1 1 0 0 0
1004 0 1 1 0 0
1005 0 0 1 1 1
1006 1 1 0 0 0
1007 0 0 0 1 0
1008 0 0 0 0 1
1009 0 1 0 1 0

If normally use this type of FORMULA:

=IF($B2=1,1,IF($C2=1,1,(IF($D2=1,1,0))))

Where B2 / C2 / D2 are the original data, but will have to copy this formula can change it for VARIABLE 2.

This isn't so much of a problem, until I have about 30 or MORE variables.

Is there anyway that this formula can be done quicker.

Thanks,
Descent73

 

[dave3009]

Perhaps something like

Excel Workbook
	A	B	C	D	E	F
1	ID	Variable_A	Variable_B	Variable_C
2	1001	1
3	1002	2	5
4	1003	1	2
5	1004	2	3
6	1005	3	5	4
7	1006	1	2
8	1007	4
9	1008	5
10	1009	2	4
11
12	INTO:
13
14		1	2	3	4	5
15	ID	Var_1	Var_2	Var_3	Var_4	Var_5
16	1001	1	0	0	0	0
17	1002	0	1	0	0	1
18	1003	1	1	0	0	0
19	1004	0	1	1	0	0
20	1005	0	0	1	1	1
21	1006	1	1	0	0	0
22	1007	0	0	0	1	0
23	1008	0	0	0	0	1
24	1009	0	1	0	1	0
Sheet2

Excel 2007

Cell Formulas
Range	Formula
B16	=IF(ISERROR(MATCH(B$14, $B2:$F2, 0)), 0, 1)
C16	=IF(ISERROR(MATCH(C$14, $B2:$F2, 0)), 0, 1)
D16	=IF(ISERROR(MATCH(D$14, $B2:$F2, 0)), 0, 1)
E16	=IF(ISERROR(MATCH(E$14, $B2:$F2, 0)), 0, 1)
F16	=IF(ISERROR(MATCH(F$14, $B2:$F2, 0)), 0, 1)

 

[energman58]

In column B next to your index number in the first row use:

=IF(ISNUMBER(MATCH(COLUMN()-1,$B2:$H2,0)),1,0)

And copy down/across (if it is in a different place you just need to adjust the COLUMN()-1 bit of the formula to suit.

This works without a need to number your columns - you can extend the range B:H as far as you need to.

 

[Descent73]

Greats thanks for both quick replies, will check them both out

 

[bosco_yip]

B16,copy and across :

=--ISNUMBER(MATCH(COLUMN()-1, $B2:$F2,))

Regards

 

[Peter_SSs]

In column B next to your index number in the first row use:

=IF(ISNUMBER(MATCH(COLUMN()-1,$B2:$H2,0)),1,0)

And copy down/across (if it is in a different place you just need to adjust the COLUMN()-1 bit of the formula to suit.

This works without a need to number your columns - you can extend the range B:H as far as you need to.

But is prone to error if new column(s) are subsequently added at the left of the sheet, as is the following:

B16,copy and across :

=--ISNUMBER(MATCH(COLUMN()-1, $B2:$F2,))

Regards