Table 1 has my list of Codes.

Table1
Code
ab
A0
B0
C3
C2
Dk
gk
35

Table 2 has my list of ID to Code Pairs

Table2
ID Code
919 AB
919 AU
919 A0
919 c2
919 C3
919 G0
919 G1
919 GK
919 HL
919 I1
311 J1
311 J6
311 M0
311 M6
311 Q0
311 C2
311 W1
311 DK
311 QV
311 IX
311 35


I am looking for a formula that will create Table 3 below - showing a count
for each ID, how many Codes are listed in Table 1. One of my issues is that
the Code can be upper and lower case - eg I could have codes AB, Ab, aB, and
ab - the formula in table 3 needs to differentiate and find an exact match.

Table3
ID CodeCount
311 2
919 2


Thank you in advance

Assumed Table 1 is in range A1:A8

Table 2 is Range C1:D20

use help column next to Table 2 .
In cell E1 put this formula and drag it down
Array function use ctrl + shift + enter

=IF(ISERROR(MATCH(TRUE,EXACT(D1,$A$1:$A$8),0)),0,1 )

and now use

Assumed Table 3 is starts at Range A10
in Cell B10 put this formula

=SUMIF(C1:C20,A12,E1:E20)



On Feb 11, 12:46*am, carl wrote:
Table 1 has my list of Codes.

Table1
Code
ab
A0
B0
C3
C2
Dk
gk
35

Table 2 has my list of ID to Code Pairs

Table2 *
ID * * *Code
919 * * AB
919 * * AU
919 * * A0
919 * * c2
919 * * C3
919 * * G0
919 * * G1
919 * * GK
919 * * HL
919 * * I1
311 * * J1
311 * * J6
311 * * M0
311 * * M6
311 * * Q0
311 * * C2
311 * * W1
311 * * DK
311 * * QV
311 * * IX
311 * * 35

I am looking for a formula that will create Table 3 below - showing a count
for each ID, how many Codes are listed in Table 1. One of my issues is that
the Code can be upper and lower case - eg I could have codes AB, Ab, aB, and
ab - the formula in table 3 needs to differentiate and find an exact match.

Table3 *
ID * * *CodeCount
311 * * 2
919 * * 2

Thank you in advance