Hello-
I have a rather complex task to solve and will do my best to explain it here.
<table border="0" cellpadding="0" cellspacing="0" width="296"><col style="width:48pt" width="64"> <col style="mso-width-source:userset;mso-width-alt:4973;width:102pt" width="136"> <col style="mso-width-source:userset;mso-width-alt:3510;width:72pt" width="96"> <tbody><tr style="height:15.0pt" height="20"> <td style="height:15.0pt;width:48pt" height="20" width="64">ID #</td> <td style="width:102pt" width="136">Car Type</td> <td style="width:72pt" width="96">Qualification</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" align="right" height="20">1</td> <td align="right">Ford
</td> <td align="right">Piston
</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" align="right" height="20">1</td> <td align="right">Ford
</td> <td align="right">Rotor
</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" align="right" height="20">1</td> <td align="right">Ford
</td> <td align="right">Bumper
</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" align="right" height="20">1</td> <td align="right">Ford
</td> <td align="right">Body
</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" align="right" height="20">2</td> <td align="right">Ford
</td> <td align="right">Piston
</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" align="right" height="20">2</td> <td align="right">Ford
</td> <td align="right">Rotor
</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" align="right" height="20">2</td> <td align="right">Chevy
</td> <td align="right">Piston
</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" align="right" height="20">2</td> <td align="right">Chevy
</td> <td align="right">Rotor
</td> </tr> </tbody></table>
In the above sample data, I know that if a certain person has Piston & Rotor qualifications for a certain car type then he is a certified engine guy on that car type. If he has qualifications Bumper & Body on a car type, he is a certified frame guy for that car type. As illustrated above, a person can hold multiple certifications on multiple car types.
What I'd like to do is add some columns which are headed:
Ford Engine Guy, Ford Frame Guy, etc. and fill them in with a 0 or 1.
To do the Ford Engine Guy formula, I need a formula that looks something like
=if(and(Car Type="Ford",and(has "Piston and Rotor" in the Qualifications column)),1,0)
The part that makes it difficult is that each person has a varying number of rows based on how many qualifications he has. Essentially, I want to return a 1 if a number of text strings which I define are found when looking across multiple rows, but I want the search to start over each time the ID# column changes to a new person.
Any ideas on how to make this work?
I have a rather complex task to solve and will do my best to explain it here.
<table border="0" cellpadding="0" cellspacing="0" width="296"><col style="width:48pt" width="64"> <col style="mso-width-source:userset;mso-width-alt:4973;width:102pt" width="136"> <col style="mso-width-source:userset;mso-width-alt:3510;width:72pt" width="96"> <tbody><tr style="height:15.0pt" height="20"> <td style="height:15.0pt;width:48pt" height="20" width="64">ID #</td> <td style="width:102pt" width="136">Car Type</td> <td style="width:72pt" width="96">Qualification</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" align="right" height="20">1</td> <td align="right">Ford
</td> <td align="right">Piston
</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" align="right" height="20">1</td> <td align="right">Ford
</td> <td align="right">Rotor
</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" align="right" height="20">1</td> <td align="right">Ford
</td> <td align="right">Bumper
</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" align="right" height="20">1</td> <td align="right">Ford
</td> <td align="right">Body
</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" align="right" height="20">2</td> <td align="right">Ford
</td> <td align="right">Piston
</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" align="right" height="20">2</td> <td align="right">Ford
</td> <td align="right">Rotor
</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" align="right" height="20">2</td> <td align="right">Chevy
</td> <td align="right">Piston
</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" align="right" height="20">2</td> <td align="right">Chevy
</td> <td align="right">Rotor
</td> </tr> </tbody></table>
In the above sample data, I know that if a certain person has Piston & Rotor qualifications for a certain car type then he is a certified engine guy on that car type. If he has qualifications Bumper & Body on a car type, he is a certified frame guy for that car type. As illustrated above, a person can hold multiple certifications on multiple car types.
What I'd like to do is add some columns which are headed:
Ford Engine Guy, Ford Frame Guy, etc. and fill them in with a 0 or 1.
To do the Ford Engine Guy formula, I need a formula that looks something like
=if(and(Car Type="Ford",and(has "Piston and Rotor" in the Qualifications column)),1,0)
The part that makes it difficult is that each person has a varying number of rows based on how many qualifications he has. Essentially, I want to return a 1 if a number of text strings which I define are found when looking across multiple rows, but I want the search to start over each time the ID# column changes to a new person.
Any ideas on how to make this work?
