Importing worksheet while checking for duplicates

SBMa

New Member
Joined
Aug 17, 2022
Messages
8
Office Version
  1. 365
  2. 2021
  3. 2019
Platform
  1. Windows
Struggling to check if duplicate rows have been imported from another workbook


VBA Code:
Sub ImportText_no_Duplicates()
' Tester !!!! Tester
' ImportText1 Macro
 Dim fileToOpen As Variant
Dim fileFilterPattern As String
Dim wsMaster As Worksheet
Dim wbTextImport As Workbook

Dim dlr As Long
Dim lr As Long
Dim lImpC As Long
Dim lImpR As Long
Dim DictDuplicates As Object
Dim countMatch As Long


Set DictDuplcicates = CreateObject("Scripting.Dictionary")

Application.ScreenUpdating = False

Set wsMaster = ThisWorkbook.Worksheets("Asset Upload Data 2022")
   
  With wsMaster
    lr = .Cells(Rows.Count, 1).End(xlUp).Offset(1).Row
    dlr = .Cells(Rows.Count, 1).End(xlUp).Row
    For i = 2 To dlr
      DictDuplicates.Add .Cells(i, 20), i
    Next i
  End With


fileFilterPattern = "Microsoft Excel Workbooks (*.xls*),*.xls*"

fileToOpen = Application.GetOpenFilename(fileFilterPattern)

' open workbook

If fileToOpen = False Then
  ' input Cancelled
  MsgBox "No file Selected."
Else

  Workbooks.OpenText _
    Filename:=fileToOpen, _
    StartRow:=2, _
    DataType:=xlDelimited, _
    Tab:=True

  Set wbTextImport = ActiveWorkbook
 
  With wbTextImport.Worksheets(1)
    lImpC = .Cells(1, .Columns.Count).End(xlToLeft).Column
    lImpR = .Cells(Rows.Count, 1).End(xlUp).Row
   
    arrData = .Range("A1:M" & lImpR).Value
  End With

  wbTextImport.Close False

End If

countMatch = 0
' Now you can work on the array
    For i = 2 To UBound(arrData) ' I'm assuming the data copied has headers, if not, change 2 for 1
        If DictDuplicates.Exists(arrData(i, 1) & """__""" & arrData(i, 2)) Then
            'If the concatenated data exists on the dictionary
            MsgBox "Duplicates found, please check data you are attempting to copy"
            countMatch = countMatch + 1
            Exit For
                       
        Else
            'If it doesn't import worksheet from a2 to last cells with data on this worksheet from lr in c
           If countMatch = 0 And i = UBound(arrData) Then
               With wbTextImport.Worksheets(1)
               .Range("A2", .Cells(lImpR, lImpC)).Copy wsMaster.Range("C" & lr)
               End With
           End If        

        End If
    Next i
 
Application.ScreenUpdating = True
'
End Sub
 
Last edited by a moderator:

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

Forum statistics

Threads
1,223,903
Messages
6,175,284
Members
452,630
Latest member
OdubiYouth

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top