[Solved] Compare data from closed workbook for new data based on values and add unique records

[kescoworksamit]

HI Greetings for the Day !
I am finding a macro that can automate things as i desired.

* I have to maintain an excel sheet that has customers data with their id.
* in other location same file is used to add daily customer walk in.
* every month i need to compile whole data in my workbook with new customers registered or what information the old customers reported.

What i want to do is -
* As i run the macro it should allow to brows where the master data exists. (flexibility to ask where is the sheet if other workbook has multiple sheets in it).
* Then compare and check the existing data on my active sheet based on ID numbers situated in Colum B. with the other workbook. (both workbook has same headers).

*data has multiple rows and columns.

*If it find any changes-- copy changes to the active sheet.
*if new ids were added to the data copy unique data and add to the active sheet with all rows and columns after last entry.

I am attaching files with result illustrations. Hope I am able to make u understand what I want.

Thanks and waiting for a kind help.

 

[Lutfor Rahman Shimanto]

HI Greetings for the Day !
I am finding a macro that can automate things as i desired.

* I have to maintain an excel sheet that has customers data with their id.
* in other location same file is used to add daily customer walk in.
* every month i need to compile whole data in my workbook with new customers registered or what information the old customers reported.

What i want to do is -
* As i run the macro it should allow to brows where the master data exists. (flexibility to ask where is the sheet if other workbook has multiple sheets in it).
* Then compare and check the existing data on my active sheet based on ID numbers situated in Colum B. with the other workbook. (both workbook has same headers).

*data has multiple rows and columns.

*If it find any changes-- copy changes to the active sheet.
*if new ids were added to the data copy unique data and add to the active sheet with all rows and columns after last entry.

I am attaching files with result illustrations. Hope I am able to make u understand what I want.

Thanks and waiting for a kind help.

Hello Kescoworksamit

Thanks for reaching out and posting your queries with such clarity. I am delighted to inform you that I have developed such a macro that full-fill your requirements. I have tested the macro several times within your provided workbooks. I found almost the same output as you mentioned in PDF.

Excel VBA Code:

Sub CompareAndUpdateData()

    Dim masterWorkbookPath As String
    Dim masterWorkbook As Workbook
    Dim masterWorksheet As Worksheet
    Dim currentWorkbook As Workbook
    Dim currentWorksheet As Worksheet
    Dim masterLastRow As Long
    Dim currentLastRow As Long
    Dim masterDataRange As Range
    Dim currentDataRange As Range
    Dim i As Long, j As Long
    Dim matchFound As Boolean
    Dim uniqueFound As Boolean
    Dim sheetName As String
   
    masterWorkbookPath = Application.GetOpenFilename("Excel Files (*.xls; *.xlsx; *.xlsm), *.xls; *.xlsx; *.xlsm", , "Select Master Workbook")
    If masterWorkbookPath = "False" Then
        Exit Sub
    End If
   
    Set masterWorkbook = Workbooks.Open(masterWorkbookPath)
   
    If masterWorkbook.Sheets.Count = 1 Then
        Set masterWorksheet = masterWorkbook.Sheets(1)
    Else
        sheetName = InputBox("Enter the sheet name in the master workbook to compare:", "Sheet Name")
        On Error Resume Next
        Set masterWorksheet = masterWorkbook.Sheets(sheetName)
        On Error GoTo 0
       
        If masterWorksheet Is Nothing Then
            MsgBox "Sheet '" & sheetName & "' not found in the master workbook!", vbExclamation
            masterWorkbook.Close SaveChanges:=False
            Exit Sub
        End If
    End If
   
    Set currentWorkbook = ThisWorkbook
    Set currentWorksheet = currentWorkbook.Sheets(1)
   
    masterLastRow = masterWorksheet.Cells(masterWorksheet.Rows.Count, "B").End(xlUp).Row
    currentLastRow = currentWorksheet.Cells(currentWorksheet.Rows.Count, "B").End(xlUp).Row
   
    Set masterDataRange = masterWorksheet.Range("A1:E" & masterLastRow)
    Set currentDataRange = currentWorksheet.Range("A1:E" & currentLastRow)
   
    Application.ScreenUpdating = False
    For i = 1 To masterDataRange.Rows.Count
        matchFound = False
        For j = 1 To currentDataRange.Rows.Count
            If masterDataRange.Cells(i, 2).Value = currentDataRange.Cells(j, 2).Value Then
                matchFound = True
                currentDataRange.Cells(j, 1).Resize(1, 5).Value = masterDataRange.Cells(i, 1).Resize(1, 5).Value
                Exit For
            End If
        Next j
       
        If Not matchFound And Application.WorksheetFunction.CountA(masterDataRange.Rows(i)) > 0 Then
            currentLastRow = currentLastRow + 1
            currentDataRange.Cells(currentLastRow, 1).Resize(1, 5).Value = masterDataRange.Cells(i, 1).Resize(1, 5).Value
        End If
    Next i
    Application.ScreenUpdating = True
   
    masterWorkbook.Close SaveChanges:=False

End Sub

Output:

OUTPUT VIDEO​

I am attaching the workbook used to explore your problem. Don't hesitate to contact us again if you have any more questions.

Regards
Lutfor Rahman Shimanto

 

[kescoworksamit]

Hi Lutfor Rahman Shimantot.

Thanks for this quick reply. It is working fine but has some limitations. I would like to ask you if you can fix it .
before getting this code i just wanted to do the same thing what it is doing but this code limits as following -

1. This code limits the user to compare the new data only from columns A:E only. As I mentioned before, my data has multiple
rows and columns.
2. It gives the flexibility to type the name of the source sheet where the new data is if the source workbook has more than one sheet. It will be more useful if the user will get an input box to choose the source sheet by clicking instead of typing the name of the source sheet.
3. I put this code in a command button to let the user get the new month data and perform the comparison. I wish to put the command button in suppose "Cummond" sheet. I wish it could ask to choose the destination sheet. (Like suppose i have more than one sheet from where i ' m running this code). Screenshot attached.

Best Regards
Amit

 

[Lutfor Rahman Shimanto]

Hi Lutfor Rahman Shimantot.

Thanks for this quick reply. It is working fine but has some limitations. I would like to ask you if you can fix it .
before getting this code i just wanted to do the same thing what it is doing but this code limits as following -

1. This code limits the user to compare the new data only from columns A:E only. As I mentioned before, my data has multiple
rows and columns.
2. It gives the flexibility to type the name of the source sheet where the new data is if the source workbook has more than one sheet. It will be more useful if the user will get an input box to choose the source sheet by clicking instead of typing the name of the source sheet.
3. I put this code in a command button to let the user get the new month data and perform the comparison. I wish to put the command button in suppose "Cummond" sheet. I wish it could ask to choose the destination sheet. (Like suppose i have more than one sheet from where i ' m running this code). Screenshot attached.

Best Regards
Amit

Dear Amit

I definitely have to praise your observations. Absolutely, the previous code contains the limitations you have mentioned. Modifying the code I provided will take some hours to reach your goal. When I am done, I will share the solution in this thread.

Thank you, once again, for staying with us. Good luck.

Regards
Lutfor Rahman Shimanto

 

[Lutfor Rahman Shimanto]

Hi Lutfor Rahman Shimantot.

Thanks for this quick reply. It is working fine but has some limitations. I would like to ask you if you can fix it .
before getting this code i just wanted to do the same thing what it is doing but this code limits as following -

1. This code limits the user to compare the new data only from columns A:E only. As I mentioned before, my data has multiple
rows and columns.
2. It gives the flexibility to type the name of the source sheet where the new data is if the source workbook has more than one sheet. It will be more useful if the user will get an input box to choose the source sheet by clicking instead of typing the name of the source sheet.
3. I put this code in a command button to let the user get the new month data and perform the comparison. I wish to put the command button in suppose "Cummond" sheet. I wish it could ask to choose the destination sheet. (Like suppose i have more than one sheet from where i ' m running this code). Screenshot attached.

Best Regards
Amit

Dear Amit

Good afternoon! I am happy to inform you that I succeeded in reaching your requirements.

I modified the previous code and applied extra features and tools to overcome the mentioned limitations. To be specific, I have developed two UserForms.

Excel VBA Procedure:

Public sheetNameMaster As String
Public sheetNameCurrent As String

Sub CompareAndUpdateDataModified()

    Dim masterWorkbookPath As String
    Dim masterWorkbook As Workbook
    Dim masterWorksheet As Worksheet
    Dim currentWorkbook As Workbook
    Dim currentWorksheet As Worksheet
    Dim masterLastRow As Long
    Dim currentLastRow As Long
    Dim masterLastCol As Long
    Dim currentLastCol As Long
    Dim masterDataRange As Range
    Dim currentDataRange As Range
    Dim i As Long, j As Long
    Dim matchFound As Boolean
    Dim uniqueFound As Boolean
    Dim sheetName As String
   
    masterWorkbookPath = Application.GetOpenFilename("Excel Files (*.xls; *.xlsx; *.xlsm), *.xls; *.xlsx; *.xlsm", , "Select Master Workbook")
    If masterWorkbookPath = "False" Then
        Exit Sub
    End If
   
    Set masterWorkbook = Workbooks.Open(masterWorkbookPath)
   
    If masterWorkbook.Sheets.Count = 1 Then
        Set masterWorksheet = masterWorkbook.Sheets(1)
    Else
        
        UserForm1.Show
        
        sheetName = sheetNameMaster
        On Error Resume Next
        Set masterWorksheet = masterWorkbook.Sheets(sheetName)
        On Error GoTo 0
       
        If masterWorksheet Is Nothing Then
            MsgBox "Sheet '" & sheetName & "' not found in the master workbook!", vbExclamation
            masterWorkbook.Close SaveChanges:=False
            Exit Sub
        End If
    
    End If
   
    Set currentWorkbook = ThisWorkbook
    If currentWorkbook.Sheets.Count = 1 Then
        Set currentWorksheet = currentWorkbook.Sheets(1)
    Else
    
        ThisWorkbook.Activate
        UserForm2.Show
        
        sheetName = sheetNameCurrent
        On Error Resume Next
        Set currentWorksheet = currentWorkbook.Sheets(sheetName)
        On Error GoTo 0
       
        If currentWorksheet Is Nothing Then
            MsgBox "Sheet '" & sheetName & "' not found in the current workbook!", vbExclamation
            masterWorkbook.Close SaveChanges:=False
            Exit Sub
        End If
    
    End If
   
    masterLastRow = masterWorksheet.Cells(masterWorksheet.Rows.Count, "B").End(xlUp).Row
    masterLastCol = masterWorksheet.Cells(1, masterWorksheet.Columns.Count).End(xlToLeft).Column
    currentLastRow = currentWorksheet.Cells(currentWorksheet.Rows.Count, "B").End(xlUp).Row
    currentLastCol = currentWorksheet.Cells(1, currentWorksheet.Columns.Count).End(xlToLeft).Column
   
    Set masterDataRange = masterWorksheet.Range(masterWorksheet.Cells(1, 1), masterWorksheet.Cells(masterLastRow, masterLastCol))
    Set currentDataRange = currentWorksheet.Range(currentWorksheet.Cells(1, 1), currentWorksheet.Cells(currentLastRow, currentLastCol))
   
    Application.ScreenUpdating = False
    
    For i = 1 To masterDataRange.Rows.Count
        matchFound = False
        For j = 1 To currentDataRange.Rows.Count
            If masterDataRange.Cells(i, 2).Value = currentDataRange.Cells(j, 2).Value Then
                matchFound = True
                currentDataRange.Cells(j, 1).Resize(1, masterLastCol).Value = masterDataRange.Cells(i, 1).Resize(1, masterLastCol).Value
                Exit For
            End If
        Next j
       
        If Not matchFound And Application.WorksheetFunction.CountA(masterDataRange.Rows(i)) > 0 Then
            currentLastRow = currentLastRow + 1
            currentDataRange.Cells(currentLastRow, 1).Resize(1, masterLastCol).Value = masterDataRange.Cells(i, 1).Resize(1, masterLastCol).Value
        End If
    Next i
    
    Application.ScreenUpdating = True
   
    masterWorkbook.Close SaveChanges:=False

End Sub

Limitation 1 (SOLVED): As you may have multiple rows and columns, the code calculates the last columns of the first row. Likewise, it determines the last row of column B of a sheet from a master workbook. You can also use a property called UsedRange. However, I recommend not using that.

Limitation 2 (SOLVED): Though choosing a sheet by typing its name is fine, you want to select a sheet by clicking the sheet name tab. This idea is more user-friendly. I totally agree with you.
To develop such a feature, I had to create a UserForm. It is for choosing a sheet from a master workbook.

UserForm1:

​

CommandButton1 (UserForm1) VBA Code:

Sub CommandButton1_Click()
    
    Dim selectedSheetName As String
    Dim extracttextbeforeexclamation As Variant
    Dim exclamationIndex As Long
    
    selectedSheetName = Me.RefEdit1.Text
    
    If selectedSheetName = "" Then
        MsgBox "Please select a sheet by clicking on the sheet name tab!", vbExclamation
        Exit Sub
    End If
        
    exclamationIndex = InStr(1, selectedSheetName, "!")
        
    If exclamationIndex > 0 Then
        extracttextbeforeexclamation = Left(selectedSheetName, exclamationIndex - 1)
    Else
        extracttextbeforeexclamation = selectedSheetName
    End If
    
    Module4.sheetNameMaster = extracttextbeforeexclamation
    
    Unload Me
    
End Sub

Limitation 3 (SOLVE): Like the solution to the second limitation, I have taken another UserForm to choose a sheet from the current workbook as a destination. Besides, I inserted a button and assigned the mentioned procedure in Module4.

UserForm2:

​

CommandButton1 (UserForm2) VBA Code:

Sub CommandButton1_Click()
    
    Dim selectedSheetName As String
    Dim extracttextbeforeexclamation As Variant
    Dim exclamationIndex As Long
    
    selectedSheetName = Me.RefEdit1.Text
    
    If selectedSheetName = "" Then
        MsgBox "Please select a sheet by clicking on the sheet name tab!", vbExclamation
        Exit Sub
    End If
        
    exclamationIndex = InStr(1, selectedSheetName, "!")
        
    If exclamationIndex > 0 Then
        extracttextbeforeexclamation = Left(selectedSheetName, exclamationIndex - 1)
    Else
        extracttextbeforeexclamation = selectedSheetName
    End If
    
    Module4.sheetNameCurrent = extracttextbeforeexclamation
    
    Unload Me
    
End Sub

Output:

OUTPUT VIDEO​

I have attached the solution workbook for better understanding. Do not hesitate to contact us if any more queries.

Regards
Lutfor Rahman Shimanto

 

[kescoworksamit]

Hi Lutfor Rahman Shimantot
Thanks a tonn... ! its working fine and so easy.
Thanks a lott.

Best Regards
Amit

 

[Lutfor Rahman Shimanto]

Hi Lutfor Rahman Shimantot
Thanks a tonn... ! its working fine and so easy.
Thanks a lott.

Best Regards
Amit

You are most welcome, Amit! Feel free to reach out again with some other exciting problem of yours.

Regards
Lutfor Rahman Shimanto