Searching& applying a dummy

**cj21** · 09-14-2010, 08:44 AM

Hi

I have attached an Excel file which illustrates my problem.

I have data with two columns. The first column is a student code. The second column tells me what subjects the student has studied in the past. In reality the dataset is huge.

I want to create 2 new variables: one called Economics and the other called Mathematics.

What I need is a formula that searches the student code and tells me whether that sudent has done Economics in the past. If they have that student gets a score of 1 for the new variable if not they get a 0. This would be the same for the Mathematics variable.

The spreadsheet attached illustrates my problem with 5 students. I have filled it out manually.I hope someone can help me.

Best wishes

Chris

**NBVC** · 09-14-2010, 09:05 AM

In XL2007 and later:

=--(COUNTIFS($A$2:$A$26,$A2,$B$2:$B$26,C$1)>0)

in Pre-XL2007:

=--(SUMPRODUCT(--($A$2:$A$26=$A2),--($B$2:$B$26=C$1))>0)

both copied down and to next column