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]

Hi & welcome to MrExcel.
One way to speed it up would be to assign the value of

Evaluate("SUMPRODUCT(COUNTIF(" & rngA.Address & "," & rngB.Address & "))")

to a variable & then test if the variable is 3 or 4.
That way you only need to run the evaluate once per row.

 

[Steviemac]

Hi & welcome to MrExcel.
One way to speed it up would be to assign the value of

Evaluate("SUMPRODUCT(COUNTIF(" & rngA.Address & "," & rngB.Address & "))")

to a variable & then test if the variable is 3 or 4.
That way you only need to run the evaluate once per row.

Thanks for the reply and your time Fluff - I never even thought about checking for both at once! - I'm not a coder

is there any chance you could alter the code so I can try it out please

 

[Fluff]

How about

Sub TaskPercentages()

Application.ScreenUpdating = False

Dim InputData As Long
Dim Result As Variant

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)

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

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

 

[Steviemac]

Fluff - Officially impressed!! - Your quick (and clever) solution knocked 6 seconds off the run time

I mean, man, You have just reduced the wait time by 37% - Ace, Thank you very much

I don't really want to ask for further help, but, would there be a way of getting rid of the loops?
I presume looping is also a massive hurdle

 

[Fluff]

Once an input row has 3 or 4 matches on one of the output rows, will it possible match other output rows?

 

[Steviemac]

yes

The input row could match many output rows with 3 or 4 matches

 

[Steviemac]

Fluff, I just tested one input row

It matched

21 rows of 60%
3 rows of 80%

 

[Fluff]

Another option that might save a bit more time is

Sub TaskPercentages()

Application.ScreenUpdating = False

Dim InputData As Long, TaskDataOutput As Long
Dim Result As Variant

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 = ""
            If Cells(TaskDataOutput, 10) = "" Or Cells(TaskDataOutput, 11) = "" Then
               Dim rngA As Range: Set rngA = Range("C" & TaskDataOutput & ":G" & TaskDataOutput)
               Dim rngB As Range: Set rngB = Range("AT" & InputData & ":AX" & InputData)
   
               Result = Evaluate("SUMPRODUCT(COUNTIF(" & rngA.Address & "," & rngB.Address & "))")
               If Not IsError(Result) Then
                  If Result = 4 Then
                     Sheets("TaskPercentComplete").Cells(TaskDataOutput, 11).Value = "80%"
                  ElseIf Result = 3 Then
                     Sheets("TaskPercentComplete").Cells(TaskDataOutput, 10).Value = "60%"
                  End If
               End If
            End If
        TaskDataOutput = (TaskDataOutput + 1)
     
       Loop
   
      InputData = (InputData + 1)
  
   Loop
  
  'nextonwardmacro
 
End Sub

This way if an output row is already flagged as 60% & 80% it won't get checked again.

 

[Steviemac]

This is interesting - Tested three times
so, even though the 60% and 80% get flagged it runs ever so slightly slower

1st code change takes 9.72 seconds
2nd code change takes 10.31 seconds