Comparing two coulmns in two workbooks - working but!

[Kruk07]

Hi

I'm using the below code to compare data in two columns in two workbooks and any cells from one column not included in the other are paste into the third workbook. I have 3 workbooks with 10 sheets in each workbook. One column from each sheet from first workbook is compared with matching column on matching sheet in second workbook and any missing entries are copied to the matching sheet on the third workbook .

Sub Compare()
Dim sh1 As Worksheet, sh2 As Worksheet, sh3 As Worksheet, lr1 As Long, lr2 As Long, rng1 As Range, rng2 As Range, C As Range
Set wbk1 = Workbooks.Open("P:\Transfer.xlsm")
Set wbk2 = Workbooks.Open("P:\Golden Source.xlsm")
Set sh1 = wbk1.Worksheets("ABC")
Set sh2 = wbk2.Worksheets("ABC")
Set sh3 = Workbooks("Comparision.xlsm").Worksheets("ABC")

lr1 = sh1.Cells(Rows.Count, 1).End(xlUp).Row 'Get the last row with data for both list sheets
lr2 = sh2.Cells(Rows.Count, 1).End(xlUp).Row
Set rng1 = sh1.Range("N2:N" & lr1) 'Establish the ranges on both sheets
Set rng2 = sh2.Range("N2:N" & lr2)
    For Each C In rng1 'Run a loop for each list ID mismatches and paste to sheet 3.
        If WorksheetFunction.CountIf(rng2, C.Value) = 0 Then
            sh3.Cells(Rows.Count, 1).End(xlUp)(2) = C.Value
        End If
    Next
    For Each C In rng2
        If Application.CountIf(rng1, C.Value) = 0 Then
            sh3.Cells(Rows.Count, 2).End(xlUp)(2) = C.Value
        End If
    Next
Windows("Transfer.xlsm").Close
Windows("Golden Source.xlsm").Close
MsgBox "Done"
End Sub

The above works great on 9 sheets but the code on the 10th is not reporting missing cells but actually is copying the whole column from both workbooks into the third workbook.

The only difference is that this 10th sheet has 55k rows where others sheet have no more than 19k rows.

The data is supplied by 10 suppliers so I thought that this might me something to do with the excel files as these reports are generated by systems that create the xlsx files as an output file and maybe this has something to do with CHAR(160) / CHAR(32) limitation so I used this formula =TRIM(CLEAN(SUBSTITUTE(E1,CHAR(160)," "))) before I run the code. The result is the same.

I also used another code as an alternative:

Sub Compare()
'UpdatebyExtendoffice 20160623
    Dim I As Long, J As Long, K As Long, M As Long
    Application.ScreenUpdating = False
    J = 1
    K = 1
    M = Cells(Rows.Count, 1).End(xlUp).Row
    Z = Cells(Rows.Count, 2).End(xlUp).Row
    Range("D1").Value = "A not in B"
    Range("E1").Value = "B not in A"
    For I = 2 To M
        If IsError(Application.match(Range("A" & I).Value, Columns("B"), 0)) Then
            J = J + 1
            Range("D" & J).Value = Range("A" & I).Value
        End If
        Next I
    For I = 2 To Z
        If IsError(Application.match(Range("B" & I).Value, Columns("A"), 0)) Then
            K = K + 1
            Range("E" & K).Value = Range("B" & I).Value
        End If
    Next I
    Application.ScreenUpdating = True
End Sub

The above codes gives me an error: Runtime error 1004. Application-defined or object-defined error. However some of the code was executed and the result is what I expected but not all lines have been compared as the code returns an error. This usually stops around the row 3k on the third workbook (result workbook).

I have spent so much time now to find the solution but nothing seems to be working.

Can someone please check and advise how this can be sorted?

Thanks

 

[Kruk07]

Anyone?

 

[pvr928]

Hi Kruk07

You seem to be asking a few questions here, without being explicit as to which question you want answered.

In summary, you have two independent blocks of code. I will address each block in turn.

First block

You mention the code 'works' for 9 sheets for not for a tenth. As your code does not have a mechanism to loop through multiple sheets, you must be manually running the code for each separate worksheet.

Consequently, and as you have already surmised yourself, the problem is likely not with the code (as it 'works' with 9 sheets), but with the data on the tenth sheet.

As you have not provide an example of what any of the sheets contain, I can only suggest you use simple logic to determine whether the data you are expecting to be the same is in fact the same. The result from something like

=[cell from Worksheet 1] = [cell from Worksheet 2]

will provide a place from which to start. A macro to iterate over the ranges may be the fastest way to achieve this.

Second block

Letting us know which line produces the error as well may help us to assist you. However, if it is likely the issue is with the underlying data, this block of code may well be redundant if the first block achieves your aim.

Cheers

pvr928

 

[Kruk07]

Hi

I will check this tomorrow and let you know.

Thanks

Tom

 

[Kruk07]

So I tested the random cells with

=[cell from Worksheet 1] = [cell from Worksheet 2]

and it was working ok. The formula returned True which means that data is matching. The vlookup also worked and the matching records were find. So the data is not the problem.

In terms of the alternative code the line that produce the error: Runtime error 1004. Application-defined or object-defined error is not highlighted so not sure which line is causing this. Any tip how to identify the line?

I think that the problem is with the number of cells to check 50k+ on each tab.

Any suggestions are welcomed.

 

[pvr928]

Hi Kruk07

The issue won't be with the number of cells per se, as 50,000 cells is not a lot of data in the scheme of things, and your variables should not be constrained (all of your counters you have dimmed as Long).

Without running your code, reading it does not suggest anything is inherently wrong. Perhaps use the Watch Window to show the value of your variables to see if you can determine at what count the code breaks (or use the Debug.Print statement in the Immediate Window). If that doesn't work, use an If - Stop statement to stop the code at increasing point, ie

If I = 10000 then Stop

then if it works up until this point:

If I = 20000 then Stop

and so on, until you can narrow down where the code is breaking.

Just some thoughts, as without having the actual spreadsheet, it is nigh on impossible to be more specific.

This isn't to do with working out what is wrong, but if you have a data set in the 50,000s you may consider using arrays rather than directly referencing the worksheet each time. It would certainly speed things up.

Cheers

pvr928

 

[Kruk07]

Thanks for the suggestions. I will try them tomorrow.

I never used arrays. Are you able to advise how arrays could be used in the first code? Also can arrays be dynamic? The number of cells is not constant and is changing when users updating the file.

I can't share the file as it contains confidential information. In few words each tab have five columns (from A to E). To be able monitor any change in the data in all cells in these five columns I had to create the unique record by formula:

=A2&B2&C2&D2&E2

The code from first post is comparing the result of the above code between two files.

I even moved the data to the same tab as I thought that this may resolve the issue. But still the same.

Thanks

 

[pvr928]

Also can arrays be dynamic?

Yes.

Are you able to advise how arrays could be used in the first code?

There are lots of references available via Google, but the most comprehensive I have come across is by Paul Kelly: The Complete Guide to Using Arrays in Excel VBA - Excel Macro Mastery

Arguably either a Collection or Dictionary may be appropriate, but I would lean towards a 2D array (but perhaps because of my greater familiarity in practice with Arrays) (and yes, Paul Kelly's site also contains similarly comprehensive guides on the Dictionary object and Collections).

Good luck!

pvr928

 

[Kruk07]

Hi

Thanks.

On the 2nd code debug is crashing at the below lines:

If IsError(Application.match(Range("A" & I).Value, Columns("B"), 0)) Then
            J = J + 1

Sometimes at the first run and some times after few runs. Not sure why.

I also debugged the first code (3 times now) and the code is crashing with the error: Runtime error 1004. Application-defined or object-defined error. 3 times this happed at the same point which is row 139. The specific cell in this row contained also text (some description) but the text was very long and irrelevant. Once the text was removed the code worked correctly.

For a time being this solution is fine for me. I will look into the arrays later as I would like to run the code quicker. Currently the operation takes around 4-5 minutes.

Many thanks