comparing multiple records across different sheets and finding differences

[mithelesh]

Hello Members

Good day!!

I am working on healthcare data. I need a help on excel.

I am attaching a file , which is an example.

I need to compare data in two sheets.

Here in example i used only 12 columns (variables) and 100 rows(records).
But in real it is upto 100 columns and 10-50 lakh records.

Problem: when i did a lookup for huge amount of data. My excel is not responding or its taking
hours to get result.

Is there any simple way or formula or technique so that i can compare the data in two sheets(the attachment contain "planning and query" sheets. i need to compare two sheets).

Planning: Planning sheet contains data where i plan the data according to specifications.
Query : the data which is implemented(implementing the planned data or run macros into to make sure the data is uploaded "xxxx database" )after planning is done.

So i need to compare the planning and query data to make sure what i implemented is correct
and also i need to find differences in records b/w planning and query document. how to resolve when it is upto 100 columns and 10-50 lakh records.

Thanks and Regards
Mithelesh.S

 

[Deepak]

Are u looking for this!!

Sub Match_Records()
Dim ws1 As Worksheet, ws2 As Worksheet
Dim i As Integer, j As Integer
Dim lastRow As Long, col As Long

Set ws1 = Sheets("Planning")
Set ws2 = Sheets("Query")

lastRow = ws2.UsedRange.Rows.Count
col = ws2.UsedRange.Columns.Count
   
Application.ScreenUpdating = False
    ws2.Cells(1, col + 1) = "Check"
    For i = 2 To lastRow
        For j = 1 To col
                If ws2.Cells(i, j).Value <> ws1.Cells(i, j).Value Then
                    ws2.Cells(i, j).Font.Bold = True
                    ws2.Cells(i, col + 1) = "Not Match"
                End If
        Next j
    Next i
Set ws1 = Nothing
Set ws2 = Nothing
Application.ScreenUpdating = True
End Sub

 

[mithelesh]

hi Deepak

Thank u for the reply.

when i changed the alignment in the query.

you can see the same data in planning and query is showing as "not match" which is not correct.( " yellow color highlighted one's " in planning and query sheet).

how can we prevent this and also

is this macro applicable when there is huge data upto 100 columns and 10-50 lakh records.

 

[Deepak]

Dear mithelesh,

For a about 12000 rows X 60 columns {about 7.07 lacs cells) it's taking 6 sec.

Means nearly 1 sec or each lac cells.

Excel 2010-2013 has limit of 10,48,576 rows so, how could you talk about 10-50 lakh records.
So, If you have about 10,00,000 X 100 records then it will take around 15-20 minutes.

Sub Match_Records1()
Dim ws1 As Worksheet, ws2 As Worksheet
Dim i As Long, j As Long
Dim lastRow As Long, col As Long
Dim mrow As Long

Set ws1 = Sheets("Planning")
Set ws2 = Sheets("Query")

lastRow = ws2.UsedRange.Rows.Count
col = ws2.UsedRange.Columns.Count
   
Application.ScreenUpdating = False
    ws2.Cells(1, col + 1) = "Check"
    For i = 2 To lastRow
        If Not IsError(Application.Match(ws2.Cells(i, 1).Value, ws1.Columns(1), 0)) Then
            mrow = Application.Match(ws2.Cells(i, 1).Value, ws1.Columns(1), 0)
            For j = 2 To col
                If ws2.Cells(i, j).Value <> ws1.Cells(mrow, j).Value Then ws2.Cells(i, col + 1) = "Not Match"
            Next j
        Else
            ws2.Cells(i, col + 1) = "Not Match"
        End If
    Next i
Set ws1 = Nothing
Set ws2 = Nothing
Application.ScreenUpdating = True
End Sub

 

[mithelesh]

Hi Deepak,

Yes it worked this time, thank you.

10-50 lakh records (i mean ,i download the query for different scenarios across different sheets and compare

with planning ). Anyway i will try to apply this logic in real time and let you know if there is any problem.

Thanks once again.

Regards
Mithelesh.S