Excel 2010 Anyway to speed up comparing 2 Sheets with 10000+ rows on each

[JasonEnsor]

Hi Guys,

I have a single workbook that has 2 sheets of data. The All_Ok sheet has 11670 rows and the Calced sheet has 17631 rows. What I am doing is for each row in All Ok (based on unique student number), finding all occurrences of that student in the calced row, the student can show up many times. Once the student is found I pass values from 4 of the cells in the calced sheet to the All Ok sheet.

What I am using is the following

My Enclose Macro sets up the environment (screen updating etc..)

Option Explicit

Sub Enclose_Macro()

    Dim StartCalc As Integer
    Dim StartScreen As Boolean
    Dim StartAlerts As Boolean
    
    ' Store Application Settings
    StartCalc = Application.Calculation
    StartScreen = Application.ScreenUpdating
    StartAlerts = Application.DisplayAlerts
    
    'Set Application Settings
    Application.Calculation = xlCalculationManual
    Application.ScreenUpdating = False
    Application.StatusBar = True
    
    ' Call Main Macro
    Call Main
    
Clean_Up:
    
    On Error Resume Next
    
    Application.Calculation = StartCalc
    Application.ScreenUpdating = StartScreen
    Application.DisplayAlerts = StartAlerts
    
    Exit Sub
    
    ' Error Handling Code Here
End Sub

I use the following function to determine the last row in the worksheets

Option Explicit

Function LastRow(sh As Worksheet)

    On Error Resume Next
    
    LastRow = sh.Cells.Find(What:="*", After:=sh.Range("A1"), _
                            LookAt:=xlPart, LookIn:=xlFormulas, _
                            SearchOrder:=xlByRows, SearchDirection:=xlPrevious, _
                            MatchCase:=False).Row
    On Error GoTo 0

End Function

Then my main module looks like this

Option Explicit

Public ws_All_OK As Worksheet
Public ws_calced As Worksheet
Public calcedLastRow As Long
Public calcedCurrentRow As Long
Public currentLastRow As Long
Public currentRow As Long
Public student As Variant

Sub Main()

    ' Set Worksheet
    Set ws_All_OK = ThisWorkbook.Sheets("Add Data")
    Set ws_calced = ThisWorkbook.Sheets("Calced")
    
    ws_All_OK.Activate
    
    currentLastRow = LastRow(ws_All_OK)
    
    For Each student In ws_All_OK.Range("A2:A" & currentLastRow)
                    
        student.Rows.EntireRow.Select
        currentRow = ActiveCell.Row
        Application.StatusBar = "Current Cell: tHEN " & currentRow
        
        ' Setup Student Id
        studentNumber = ws_All_OK.Range("A" & currentRow).Value
        
        
        ws_calced.Activate
        calcedLastRow = LastRow(ws_calced)
        For Each calcedStudent In ws_calced.Range("A2:A" & calcedLastRow)
            
            calcedStudent.Rows.EntireRow.Select
            calcedCurrentRow = ActiveCell.Row
            
            ' Set Test ID
            testNumber = ws_calced.Range("A" & calcedCurrentRow).Value
            
            If (StrComp(studentNumber, testNumber, vbTextCompare) = 0) Then
                ws_All_OK.Range("C" & currentRow).Value = ws_All_OK.Range("C" & currentRow).Value + ws_calced.Range("B" & calcedCurrentRow).Value
                ws_All_OK.Range("D" & currentRow).Value = ws_calced.Range("C" & calcedCurrentRow).Value
                ws_All_OK.Range("E" & currentRow).Value = ws_calced.Range("D" & calcedCurrentRow).Value
                ws_All_OK.Range("F" & currentRow).Value = ws_calced.Range("E" & calcedCurrentRow).Value
            End If
            
        Next calcedStudent

        ws_All_OK.Activate
    Next student
End Sub

I know I should change the scope of my variables for the main method, but I am assuming that won't be making much of a difference speed wise. There is a lot of data being compared here and I still need to calculate other values from various sheets on the workbook, so I am hoping I can speed this section up, then just build the extra functionality in.

Many thanks in advance for any tips and ideas

Regards

J.

 

[SkipVought]

hi,

the student can show up many times. Once the student is found I pass values from 4 of the cells in the calced sheet to the All Ok sheet.

Your code seems to assume that there could be FOUR values for any student, written to columns C:F.

The code also seems to assume that when StudentNumber on ALL = TestNumber on calc, then the 4 values on that row B:E, are written the 4 columns on the corresponding ALL row to columns C:F.

Bottom Line: you're only saving the LAST occurrence of that student, and ignoring the "many" that preceeded.

Am I missing something?



Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

Also, if you want this kind of help, PLEASE declare ALL your variables before posting code.

Also, if you want this kind of help, PLEASE post sample data to aid in the process.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[DaveInIowa]

The first thing to refactor is to eliminate .Activate and .Select. Reference the cells you want to examine or update via ranges.

Are your worksheets sorted by student number? If so, you don't need to begin at the first data row of your "Calced" sheet every time or continue to the end once you're past the current student.

 

[JasonEnsor]

Hi Skip,

Appologies for the undeclared variables and lack of data

The Add Data has the following:
Headers: ID | Name | Item Amount | Class Nbr | Fee Code | Group | Career
1 | Jason| | | | | PGR

The Calced has
Headers: ID | Item Amount | Class Nbr | Fee Code | Group | Semester
1 | 990.0 | 123 | GRP003 | UH3AA | A
1 | 1800.0 | 123 | GRP003 | UH3AA | S

There will be four values written for each student, the Item Amount that is found for each student with the same number is totalled up in the spreadsheet using

ws_All_OK.Range("C" & currentRow).Value = ws_All_OK.Range("C" & currentRow).Value + ws_calced.Range("B" & calcedCurrentRow).Value

the only value that changes that I am in need of acquiring is the sum of Item Amount for each student.

I hope that makes sense. I know that this isn't possibly the best way to acquire the sum of data but as this data but atleast it is automated for front facing users, preventing them having to remember formula etc...

I just don't want it to take hours to process

Thanks Again

Regards

J.

 

[SkipVought]

@Dave. The OP has a very basic flaw in his logic, so improving the performance simply hastens the flawed result!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[JasonEnsor]

Dave,

Apologies for being a little Dim on this but how would I get the current row without Selecting the ActiveCell? I assumed I had to activate the worksheets in order to get and pass data to them. This is the way I have always done it, if you could give me a better example I would be grateful as I am always looking to learn more.

The worksheets are not sorted by student number but I could sort them that way, however as students can appear on the Calced sheet more than once how would I determine that the student will not appear again? as the number of times a student appears varies.

Skip,

Here is the Main Sub again with declared variables

Sub Main()

Dim StudentNumber As String
Dim calcedStudent As Variant
Dim testNumber As String
Dim student As Variant

    ' Set Worksheet
    Set ws_All_OK = ThisWorkbook.Sheets("Add Data")
    Set ws_calced = ThisWorkbook.Sheets("Calced")
    
    ws_All_OK.Activate
    
    currentLastRow = LastRow(ws_All_OK)
    
    For Each student In ws_All_OK.Range("A2:A" & currentLastRow)
                    
        student.Rows.EntireRow.Select
        currentRow = ActiveCell.Row
        Application.StatusBar = "Current Cell: " & currentRow
        
        ' Setup Student Id
        StudentNumber = ws_All_OK.Range("A" & currentRow).Value
        
        
        ws_calced.Activate
        calcedLastRow = LastRow(ws_calced)
        For Each calcedStudent In ws_calced.Range("A2:A" & calcedLastRow)
            
            calcedStudent.Rows.EntireRow.Select
            calcedCurrentRow = ActiveCell.Row
            
            ' Set Test ID
            testNumber = ws_calced.Range("A" & calcedCurrentRow).Value
            
            If (StrComp(StudentNumber, testNumber, vbTextCompare) = 0) Then
                ws_All_OK.Range("C" & currentRow).Value = ws_All_OK.Range("C" & currentRow).Value + ws_calced.Range("B" & calcedCurrentRow).Value
                ws_All_OK.Range("D" & currentRow).Value = ws_calced.Range("C" & calcedCurrentRow).Value
                ws_All_OK.Range("E" & currentRow).Value = ws_calced.Range("D" & calcedCurrentRow).Value
                ws_All_OK.Range("F" & currentRow).Value = ws_calced.Range("E" & calcedCurrentRow).Value
            End If
            
        Next calcedStudent

        ws_All_OK.Activate
    Next student
End Sub

Also am I right in declaring my student and calcedstudent as variants, or should I be using another data type?

Many Thanks

Regards

J.

 

[PHV]

how would I get the current row without Selecting the ActiveCell
Replace this:
student.Rows.EntireRow.Select
currentRow = ActiveCell.Row
with this:
currentRow = student.Row

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SkipVought]

Yet another possibility would be, or this relatively small amount of data, the simply do spreadsheet SUMIF() and VLOOKUP or INDEX & MATCH.

If you used Structured Tabels, it would be even simpler to maintain as your ALL sheet added rows. NO VBA REQUIRED!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

column C
=SUMIF(Calced[Headers: ID],[Headers: ID],Calced[Item Amount])

column D:F
=INDEX(Calced[Class Nbr],MATCH([Headers: ID],Calced[Headers: ID],0),1)
=INDEX(Calced[Fee Code],MATCH([Headers: ID],Calced[Headers: ID],0),1)
=INDEX(Calced[Group],MATCH([Headers: ID],Calced[Headers: ID],0),1)

where the Calced sheet Structrued Table is named Calced.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}