Formula Help

[treeleaf20]

All,
Say I have the following Data Set:

<table>
<tr><td>Num</td><td>Category</td></tr>
<tr><td>12345</td><td>AA</td></tr>
<tr><td>12345</td><td>AB</td></tr>
<tr><td>67890</td><td>AA</td></tr>
<tr><td>81737</td><td>AB</td></tr>
<tr><td>28748</td><td>AA</td></tr>
<tr><td>28748</td><td>AB</td></tr>
</table>

I'm trying to find a formula to say check the number in the Num and find out if it has an AA and a AB segment. If it has both then basically say True, if it's missing one, put which one it's missing. So my results would look something like this:

<table>
<tr><td>Num</td><td>Category</td></tr>
<tr><td>12345</td><td>AA</td><td>True</td></tr>
<tr><td>12345</td><td>AB</td><td>True</td></tr>
<tr><td>67890</td><td>AA</td><td>AB</td></tr>
<tr><td>81737</td><td>AB</td><td>AA</td></tr>
<tr><td>28748</td><td>AA</td><td>True</td></tr>
<tr><td>28748</td><td>AB</td><td>True</td></tr>
</table>

Any help would be greatly appreciated. Thanks in advance.

 

[Robert Mika]

Your data in A2:B7 :

C1:AA

D1:AB


C2 copy down:

=IF(AND(SUMPRODUCT(--($A$2:$A$7=A2),--($B$2:$B$7=$D$1))=1,SUMPRODUCT(--($A$2:$A$7=A2),--($B$2:$B$7=$C$1))=1),"true",IF(AND(SUMPRODUCT(--($A$2:$A$7=A2),--($B$2:$B$7=$D$1))=1,SUMPRODUCT(--($A$2:$A$7=A2),--($B$2:$B$7=$C$1))<>1),"AA","AB"))