check how many in a range from another range...
 

Hi there, I need a bit of help,
If I have a range as the example below:

Range to check:
cells A1 B1 C1 D1 E1
value D G J K N

Range to check against:
cells H1 I1 J1 K1 L1
value D E G J K

I need to know how many matches there are ie
0 matches
1 match
2 matches
3 matches
4 matches
5 matches
The results in O1:T1

The twist is that the matches are only to be in the matching
position...ie the example above would only have one match, which
is "D" (A1 and H1)...even though there are four repeated values...
they are not in the matching postion, I am going to have to do this
lots and lots of times down a list. I want to try and do that bit...
Anybody know a fast way to check this?

thanks

ste

check how many in a range from another range...
 

O1: =IF(A1=H1,"Match","")
Copy across to S1
T1: = Countif(O1:S1,"Match")& " matches"

or directly
T1: = =SUMPRODUCT(--(A1:E1=H1:L1))& " matches"

--

HTH

RP
(remove nothere from the email address if mailing direct)


"ste mac" wrote in message
m...
Hi there, I need a bit of help,
If I have a range as the example below:

Range to check:
cells A1 B1 C1 D1 E1
value D G J K N

Range to check against:
cells H1 I1 J1 K1 L1
value D E G J K

I need to know how many matches there are ie
0 matches
1 match
2 matches
3 matches
4 matches
5 matches
The results in O1:T1

The twist is that the matches are only to be in the matching
position...ie the example above would only have one match, which
is "D" (A1 and H1)...even though there are four repeated values...
they are not in the matching postion, I am going to have to do this
lots and lots of times down a list. I want to try and do that bit...
Anybody know a fast way to check this?

thanks

ste

check how many in a range from another range...
 

Sub BB()
Dim rng As Range
Dim i As Long, Cell As Range
Set rng = Range(Cells(1, 1), Cells(1, 1).End(xlDown))
For Each Cell In rng
For i = 1 To 5
If Cell(1, i).Value = Cell(1, i + 7).Value Then
Cell(1, i + 14).Value = "Match"
Else
Cell(1, i + 14).Value = "No Match"
End If
Next
Cell(1, 20).FormulaR1C1 = "=Countif(RC[-5]:RC[-1],""Match"")"
Next
End Sub

Is a guess at What you want.

Assumes your data starts in A1.

Count of matches is in Column T.
--
Regards,
Tom Ogilvy



"ste mac" wrote in message
m...
Hi there, I need a bit of help,
If I have a range as the example below:

Range to check:
cells A1 B1 C1 D1 E1
value D G J K N

Range to check against:
cells H1 I1 J1 K1 L1
value D E G J K

I need to know how many matches there are ie
0 matches
1 match
2 matches
3 matches
4 matches
5 matches
The results in O1:T1

The twist is that the matches are only to be in the matching
position...ie the example above would only have one match, which
is "D" (A1 and H1)...even though there are four repeated values...
they are not in the matching postion, I am going to have to do this
lots and lots of times down a list. I want to try and do that bit...
Anybody know a fast way to check this?

thanks

ste

check how many in a range from another range...
 

Bob, Tom all I can say is thanks..now I have something to
start me off, I can get going... cheers

ste