I have the follwing data in A1:T17
3,4,7,9,10,11,21,32,33,35,37,41,47,57,60,64,69,72,74,75
4,6,9,10,12,13,15,21,22,31,35,47,49,52,56,63,64,72,74,75
4,6,9,10,15,21,31,33,34,41,42,45,46,47,57,60,68,72,74,78
3,4,6,10,11,13,18,21,30,32,33,35,46,53,60,67,69,74,77,78
6,9,13,16,21,22,31,46,48,49,52,61,63,64,69,70,71,75,78,79
3,4,7,10,14,17,18,21,28,31,33,36,37,43,47,57,65,69,75,80
4,7,13,15,17,25,29,32,37,42,45,47,50,57,60,64,68,71,72,74
3,7,10,11,16,18,28,34,35,43,47,51,52,55,56,57,60,64,71,72
8,9,10,12,16,21,22,28,38,47,49,51,52,53,54,55,64,66,71,72
4,5,6,9,12,15,19,20,30,34,35,38,45,47,54,56,63,65,72,78
5,6,9,12,15,21,26,31,32,43,44,47,64,66,67,68,69,74,75,80
4,7,9,10,11,20,28,29,30,32,34,35,40,41,49,52,66,69,70,74
3,4,8,10,14,20,21,23,28,29,32,37,44,47,48,49,56,64,69,72
1,6,9,10,11,13,21,25,29,33,36,43,48,49,51,52,63,65,72,74
1,3,7,11,14,18,27,33,35,37,39,41,45,47,48,53,64,65,75,77
3,4,5,6,11,13,15,18,28,29,35,56,61,63,64,69,71,74,75,80
3,13,15,21,24,27,28,35,47,48,49,54,56,57,63,72,75,76,77,79
I want to compare the first set of 20 numbers (A1:T1) with the second set (A2:T2) and check how many numbers match. ** If the matched numbers
are >=10 ** then list them to the right of cell W1. In this example, 10 numbers matched are 4,9,10,21,35,47,64,72,74,75. List them in W1:AF1.
Now compare (A1:T1) with (A3:T3). In this example, 11 numbers matched 4,9,10,21,33,41,47,57,6,72,74 list them in W2:AG2.
Now compare (A1:T1) with (A4:T4). 10 numbers matched 3,4,10,11,21,32,33,35,60,69 list them in W3:AF3.
Compare (A1:T1) with (A5:T5). Here only 5 numbers matched 9,21,64,69,75 (Which is ** less than 10 ** - does not match the criteria) DO NOT LIST
this.
Compare (A1:T1) with (A6:T6). 11 numbers matched, 3,4,7,10,21,33,37,47,57,69,75 list them in cell W4:AG4.
Go on comparing (A1:T1) with all other sets of 20 numbers till (A17:T17).
Once finished, start comparing (A2:T2) with (A3:T3), then (A2:T2) with (A4:T4) ..... (A2:T2) with (A17:T17).
Once finished, start comparing (A3:T3) with (A4:T4), then (A3:T3) with (A5:T5) ..... (A3:T3) with (A17:T17).
Go on doing this till (A16:T16) with (A17:T17).
Sub Compare()
Dim r As Integer, rr As Integer, c As Integer, cc As Integer, opc As
Integer, opr As Long
opr = 1
For r = 1 To 16
For rr = r + 1 To 17
opc = 23
For c = 1 To 20
For cc = 1 To 20
If Cells(r, c) = Cells(rr, cc) Then
Cells(opr, opc) = Cells(r, c)
opc = opc + 1
End If
Next cc
Next c
If WorksheetFunction.Count(Range("W" & opr & ":AO" & opr)) < 10 Then
Range("W" & opr & ":AO" & opr).ClearContents
Else
opr = opr + 1
End If
Next rr
Next r
End Sub
Display More
The above code runs fine but If I increase my rows from 17 to 1200 it takes 3 1/2 hours to complete. Can it be reduced?