Matching letters and then counting

  • Thread starter Thread starter sam
  • Start date Start date
S

sam

OK Guys I have a problem that I have tried to solve but coming up with nothing.

Below is a sample of the raw data that I have. It is a mixture of letters
and numbers.

Raw Data
O
O
L0
L2
C

I have to match these letters to corrosponding letters from a different data
set:

1st Code 2nd Code
X N,K,L0
S C
U D
M A
R E
C H,O,L2,B2

So the anser would be:

X= 1
S= 1
U= 0
M= 0
R= 0
C= 3

Any help would be appreciated. Thanks,

Sam.
 
I think you need a formula for each code. Here ar two versions.

for code x

=SUMPRODUCT(--(A2:A6="n")+(A2:A6="k")+(A2:A6="L0"))

For code C
=SUM(IF(A2:A6={"H","O","L2","B2"},1,0))

The second is an array formula, enter it with Cntrl + Shift + Enter (CSE)

Excel will enclose the formula with curly braces.

HTH
Press "yes" if it does.

Peter
 
Counting Letters is fairly easy.

I'm having problems understanding the MATCHING part of your problem.

Can you explain what you would do manually to achieve the desired results.
 
Back
Top