Compare Data in Two Ranges Row by Row and Count Matches

[Steviemac]

Hi there, first time poster, long time lurker

Hoping some kind soul can speed up this macro with a light speed modification

The data to check (InputData) is replaced many times at this part of the process and at the rate the InputData comes in is
proving to be a real bottleneck, the macro works fine, it's just that it takes 16 seconds to run each time, which is too long
Hopefully there is a better faster way (maybe a macro rewrite!)

Data is held in two data sets, both 5 columns across
InputData set is 56 rows (variable)
TaskPercentComplete set is 2000 rows (variable)

Every row in the 'InputData' table is compared with Each row in turn in the 'TaskPercentComplete' table
for matches - it only has to record whether there is a match of 3 values and/or a match of 4 values

Any help is very much appreciated

Thanks Ste

Sub TaskPercentages()

Application.ScreenUpdating = False

Dim InputData As Long

Sheets("TaskPercentComplete").Range("J3:L10000").ClearContents

 InputData = 2
 
   Do While Not Sheets("TaskPercentComplete").Cells(InputData, 46).Value = ""
  
       TaskDataOutput = 3
                 
        Do While Not Sheets("TaskPercentComplete").Cells(TaskDataOutput, 3).Value = ""
       
            Dim rngA As Range: Set rngA = Range("C" & TaskDataOutput & ":G" & TaskDataOutput)
            Dim rngB As Range: Set rngB = Range("AT" & InputData & ":AX" & InputData)

            If Evaluate("SUMPRODUCT(COUNTIF(" & rngA.Address & "," & rngB.Address & "))") = 4 Then
            Sheets("TaskPercentComplete").Cells(TaskDataOutput, 11).Value = "80%"
            End If
           
            If Evaluate("SUMPRODUCT(COUNTIF(" & rngA.Address & "," & rngB.Address & "))") = 3 Then
            Sheets("TaskPercentComplete").Cells(TaskDataOutput, 10).Value = "60%"
            End If

        TaskDataOutput = (TaskDataOutput + 1)
     
       Loop
   
      InputData = (InputData + 1)
  
   Loop
  
  'nextonwardmacro
 
End Sub

 

[Fluff]

Obviously the cost of checking both cells every iteration is more expensive than just running the evaluate.

 

[Steviemac]

Yep, that seems to be the cost, I have searched the internet for ages looking for a solution

The 37% reduction in runtime you made is a massive increase - brilliant!

All I can say is thanks Fluff, your help has been invaluable
(if something comes to mind, let me know

Thanks mate

 

[Fluff]

You're welcome & thanks for the feedback

 

[Fluff]

As I've nothing to test on, I don't know if this will be an improvement or not because of the multiple nested loops. But running everything in memory is normally a lot faster than looping through cells

Sub Steviemac()
   Dim Iary As Variant, Oary As Variant, Nary As Variant
   Dim r As Long, c As Long, rr As Long, cc As Long, i As Long
   
   With Sheets("TaskPercentComplete")
      Iary = .Range("AT2").CurrentRegion.Value2
      Oary = .Range("C3").CurrentRegion.Value2
   End With
   ReDim Nary(1 To UBound(Oary), 1 To 2)
   
   For r = 1 To UBound(Iary)
      For rr = 1 To UBound(Oary)
         For c = 1 To 5
            For cc = 1 To 5
               If Iary(r, c) = Oary(rr, cc) Then i = i + 1
            Next cc
         Next c
         If i = 3 Then Nary(rr, 1) = "60%"
         If i = 4 Then Nary(rr, 2) = "80%"
         i = 0
      Next rr
   Next r
   Sheets("TaskPercentComplete").Range("J3").Resize(UBound(Oary), 2).Value = Nary
End Sub

 

[Steviemac]

Fluff... I am blown away - literally - it now takes 0.33 seconds, tested many times and it's consistently 0.33 seconds
and that's including the timer!! - phenomenal piece of code

it's gone from 16 seconds to 9.7 seconds to three times a second

As you obviously don't have the sheet I tested the sheet for counting correctly
It's exact, the 60% and the 80% are counted bang on


It just needs a little tweak (I hope it's little)
The output is correct but placed two rows down from the matched data

60% count is correct - just two rows down from the match

Exactly the same with the 80%

many many thanks

Ste

 

[Fluff]

Does your output range start at C3?

 

[Steviemac]

Yes, C3 is the start of the output range

 

[Fluff]

In that case I'm not sure why you are getting that. It outputs the % on the correct rows for me.

+Fluff 1.xlsm
	C	D	E	F	G	H		J	K	L																																		AT	AU	AV	AW	AX
1
2																																												729	77	571	160	708
3	141	53	137	10	658				80%																																			19	662	264	886	601
4	886	311	19	738	662			60%																																				141	53	578	10	658
5	926	706	599	82	600																																							539	525	984	858	67
6	229	16	462	842	326				80%																																			631	947	487	69	988
7	610	135	125	566	815																																							642	617	27	51	164
8	742	438	477	691	106																																							183	846	20	676	278
9	899	428	878	919	65																																							16	229	326	462	440
10	410	418	926	536	125																																							496	83	960	24	328
Main

 

[Steviemac]

Yours look good, it must be something my end, but it's definitely doing it

oh, that's interesting, let me wipe everything out and do it afresh

 

[Fluff]

Do you have any data in C1:G2 or AT1:AX1?