VBA Help required to speed up the code

[motherindia]

Hi Mick Sir,

Thanks for provding the very beautiful piece of code.

http://www.mrexcel.com/forum/excel-...-end-date-range-between-2-sheets-each-id.html


I tried the above with real data. The sheet contains 2-3 lakh rows in 2 sheets. The code was running for 2 hrs and still running.

Is there way to speed up this.
please help me

Regards,
mother india

 

[mole999]

Do you have application.screenupdating = false anywhere in your code

 

[motherindia]

Hi Sir,

Thanks for quick responses. I tried that option as well. It's very very slow.
Is there way to speed up.

Regards,
mother india.

 

[kalak]

Hi Sir,

Thanks for quick responses. I tried that option as well. It's very very slow.
Is there way to speed up.

Regards,
mother india.

Maybe if you state on this thread just what you want then an adequate solution might be forthcoming.

 

[motherindia]

Hi Kalak,

Thanks for quick responses.
Basically the current macro compares 2 set of data. The comparison based on ID , Start date, End Date, Days between 2 sheets. The macro works fine if I running it for 5k rows and it takes 5- 10 min.

However, the real data has big challenge as I have huge rows to compare. The rows are about 2-3 lakh rows in each sheet.

I tried with application.screeupdatingmethod = false etc.... Still no luck. is there way to speed up the existing code.

if I try on huge data macro keeps running for more than 1hr and I will debug it to stop.


Thanks ,
motherininda.

 

[kalak]

Hi Kalak,

Thanks for quick responses.
Basically the current macro compares 2 set of data. The comparison based on ID , Start date, End Date, Days between 2 sheets. The macro works fine if I running it for 5k rows and it takes 5- 10 min.

However, the real data has big challenge as I have huge rows to compare. The rows are about 2-3 lakh rows in each sheet.

I tried with application.screeupdatingmethod = false etc.... Still no luck. is there way to speed up the existing code.

if I try on huge data macro keeps running for more than 1hr and I will debug it to stop.


Thanks ,
motherininda.

Please post a small sample which is typical of your data, apart from the number of rows.

What is the nature of the comparison you want? e.g. spelling errors, location in row, in column, missing data, other ...?

 

[motherindia]

Sheet1 contains following

E# S Date E Date Days worked Type
9820559 17-Aug-15 17-Aug-15 1.00 NW
9820559 21-Aug-15 22-Aug-15 1.50 NW
9820559 29-Aug-15 31-Aug-15 3.00 NW
9820559 01-Sep-15 01-Sep-15 1.00 NW
9904557 17-Aug-15 17-Aug-15 1.00 NW
9904557 29-Aug-15 31-Aug-15 3.00 NW
9913888 29-Aug-15 31-Aug-15 3.00 NW

Sheet2

E# S Date E Date Days worked Type
9820559 18-Aug-15 20-Aug-15 3.00 NW
9820559 21-Aug-15 21-Aug-15 1.00 NW
9820559 22-Aug-15 28-Aug-15 7.00 NW
9820559 29-Aug-15 31-Aug-15 3.00 NW
9820559 01-Sep-15 01-Sep-15 1.00 NW
9904557 01-Sep-15 01-Sep-15 1.00 NW

Shee3 (Output as expected below)

E# S Date E Date Days Remarks
9820559 17-Aug-15 17-Aug-15 1 Not in Shee2
9820559 22-Aug-15 22-Aug-15 0.5 Not in Shee2
9820559 18-Aug-15 20-Aug-15 3 Not in Shee1
9820559 22-Aug-15 28-Aug-15 7 Not in Shee1
9904557 17-Aug-15 17-Aug-15 1 Not in Shee2
9904557 29-Aug-15 31-Aug-15 3 Not in Shee2
9904557 01-Sep-15 01-Sep-15 1 Not in Shee1
9913888 29-Aug-15 31-Aug-15 3 Not in Shee2

Regards,
motherindia

 

[kalak]

Sheet1 contains following

E# S Date E Date Days worked Type
9820559 17-Aug-15 17-Aug-15 1.00 NW
9820559 21-Aug-15 22-Aug-15 1.50 NW
9820559 29-Aug-15 31-Aug-15 3.00 NW
9820559 01-Sep-15 01-Sep-15 1.00 NW
9904557 17-Aug-15 17-Aug-15 1.00 NW
9904557 29-Aug-15 31-Aug-15 3.00 NW
9913888 29-Aug-15 31-Aug-15 3.00 NW

Sheet2

E# S Date E Date Days worked Type
9820559 18-Aug-15 20-Aug-15 3.00 NW
9820559 21-Aug-15 21-Aug-15 1.00 NW
9820559 22-Aug-15 28-Aug-15 7.00 NW
9820559 29-Aug-15 31-Aug-15 3.00 NW
9820559 01-Sep-15 01-Sep-15 1.00 NW
9904557 01-Sep-15 01-Sep-15 1.00 NW

Shee3 (Output as expected below)

E# S Date E Date Days Remarks
9820559 17-Aug-15 17-Aug-15 1 Not in Shee2
9820559 22-Aug-15 22-Aug-15 0.5 Not in Shee2
9820559 18-Aug-15 20-Aug-15 3 Not in Shee1
9820559 22-Aug-15 28-Aug-15 7 Not in Shee1
9904557 17-Aug-15 17-Aug-15 1 Not in Shee2
9904557 29-Aug-15 31-Aug-15 3 Not in Shee2
9904557 01-Sep-15 01-Sep-15 1 Not in Shee1
9913888 29-Aug-15 31-Aug-15 3 Not in Shee2

Regards,
motherindia

You can try this.

Sub in_different_sheets()

Dim d As Object, u(1 To 10 ^ 6, 1 To 1), a, c
Dim i As Long, j As Long, k As Long, s as Long
Dim x As String, y As String
Set d = CreateObject("scripting.dictionary")

For k = 1 To 2
x = "sheet" & k
y = "sheet" & 3 - k
d.RemoveAll

a = Sheets(x).Cells(1).CurrentRegion
For i = 2 To UBound(a, 1)
    c = ""
    For j = 1 To UBound(a, 2)
        c = c & "|" & a(i, j)
    Next j
    d(c) = 1
Next i

a = Sheets(y).Cells(1).CurrentRegion
For i = 2 To UBound(a, 1)
    c = ""
    For j = 1 To UBound(a, 2)
        c = c & "|" & a(i, j)
    Next j
    If Not d(c) = 1 Then
        s = s + 1
        u(s, 1) = Mid(c, 2) & "|" & "Not in " & x
    End If
Next i
Next k
With Sheets("sheet3").Cells(1).Resize(s)
    .Value = u
    .TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar:= _
        "|", FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), _
        Array(6, 1), Array(7, 1)), TrailingMinusNumbers:=True
End With

End Sub

 

[motherindia]

Hi Kalak,
Thanks a ton for providing piece of codes. It works at amazing speed.
Your code check for the exact match of start date and end date against each id. However I want to compare each day between date ranges from sheet1 with date range of sheet2 and provide only mismatch on 3rd Sheet.

In my example given above ;
Sheet1 for E# 9820559

9820559 21-Aug-15 22-Aug-15 1.50 NW
In Sheet2 for E# 9820559

9820559 21-Aug-15 21-Aug-15 1.00 NW

Here the mis match is only for 22nd Aug and for half day and 21st Aug is matching in both sheet.
Hence the result should be;

9820559 22-Aug-15 22-Aug-15 0.5 Not in Shee2 and not like below;

9820559 21-Aug-15 21-Aug-15 1 NW not in sheet1
9820559 21-Aug-15 22-Aug-15 1.5 NW Not in sheet2







Regards,
motherindia.

 

[Oeldere]

contains 2-3 lakh rows in 2 sheets

What is the amount of 2 - 3 lakh

200
2.000
20.000
200.000
2.000.000

other?