My code below find the duplicates based on 2 criteria:The first criteria is the Name in Column AThe second criteria is the Country in Column DSheet(RawData) :Sheets(Conso) after the macro:What I am looking is to increase the speed of this code, because I am working with more than 150K rows and it takes hours.Sub MDMDuplicates()Dim WB As WorkbookDim wsRawData As Worksheet, wsConso As WorksheetDim i As Long, j As Long, Lastrow As Long, LastrowConso as LongDim SupNameToCheck As String, ConsoSupplierDUNS As String, ConsoSupplierMDM As String, ConsoSupplierNAME As StringSet WB = ThisWorkbookSet wsRawData = WB.Sheets(RawData)Set wsConso = WB.Sheets(Conso)Lastrow = wsRawData.Range(A & Rows.Count).End(xlUp).RowApplication.ScreenUpdating = FalseWith wsRawDataFor i = 2 To LastrowSupNameToCheck = .Cells(i, A).ValueSupCountryToCheck = .Cells(i, D)ConsoSupplierDUNS = ConsoSupplierMDM = ConsoSupplierNAME = j = Lastrow    Do        If i <> j And SupNameToCheck = .Cells(j, A) And SupCountryToCheck = .Cells(j, D) Then            If ConsoSupplierNAME =  Then            ConsoSupplierDUNS = .Cells(i, B) & , & .Cells(j, B)            ConsoSupplierMDM = .Cells(i, C) & , & .Cells(j, C)            ConsoSupplierNAME = SupNameToCheck & , & .Cells(j, A)            Else            ConsoSupplierDUNS = .Cells(j, B) & , & ConsoSupplierDUNS            ConsoSupplierMDM = .Cells(j, C) & , & ConsoSupplierMDM            ConsoSupplierNAME = .Cells(j, A) & , & ConsoSupplierNAME            End If           .Cells(j, A).EntireRow.Delete        End If        j = j - 1      Loop Until j = 1LastrowConso = wsConso.Range(A & Rows.Count).End(xlUp).Row + 1If Not ConsoSupplierNAME =  Then        wsConso.Cells(LastrowConso, B) = ConsoSupplierDUNS        wsConso.Cells(LastrowConso, C) = ConsoSupplierMDM        wsConso.Cells(LastrowConso, A) = ConsoSupplierNAME    Else        wsConso.Cells(LastrowConso, B) = .Cells(i, B)        wsConso.Cells(LastrowConso, C) = .Cells(i, C)        wsConso.Cells(LastrowConso, A) = SupNameToCheck End IfNext iApplication.ScreenUpdating = TrueEnd WithEnd Sub
Find Duplicate with 2 criteria
vba;excel
It looks to me that there is repeated loops cycling through the entire data matrix trying to identify duplicates. Putting the whole data block into a two-dimensional variant array would help. Faster lookups could be achieved with WorksheetFunction object's use of the MATCH function but a Scripting.Dictionary object with its unique collection index can greatly reduce the looping and the lookups.I would heartily recommend either setting the VBE's Tools  Options  Editor  Require variable declaration or manually putting Option Explicit at the top of each module code sheet. Currently, the SupCountryToCheck var was left undeclared.Sub MDM_Duplicates_Jeeped()    Dim wb As Workbook, wsRawData As Worksheet, wsConso As Worksheet    Dim sKey As String, tmp As Variant    'late binding of the dictionary object    Dim d As Long, vTMPs As Variant, dMDMs As Object    'early binding of the dictionary object (see footnote )    'Dim d As Long, vTMPs as variant, dMDMs As new Scripting.Dictionary    appTGGL bTGGL:=False    'turn off unnecessary environment overhead    Set wb = ThisWorkbook    Set wsRawData = wb.Worksheets(RawData)    Set wsConso = wb.Worksheets(Conso)    'late binding of the dictionary object    Set dMDMs = CreateObject(Scripting.Dictionary)    dMDMs.CompareMode = vbTextCompare    With wsRawData        'dump all of the values into a 2-D variant array        vTMPs = .Range(.Cells(2, 1), .Cells(Rows.Count, 4).End(xlUp)).Value2        'for testing purposes - first 50 rows        'vTMPs = .Range(.Cells(2, 1), .Cells(50, 4)).Value2    End With    'populate the dictionary with name/country pair keys and    'comma separated duns/product concatenated item pairs    For d = LBound(vTMPs, 1) To UBound(vTMPs, 1)        sKey = Join(Array(vTMPs(d, 1), vTMPs(d, 4)), ChrW(8203))        If dMDMs.Exists(sKey) Then            dMDMs.Item(sKey) = Join(Array(Split(dMDMs.Item(sKey), ChrW(8203))(0) & Chr(44) & Format(vTMPs(d, 2), 000000000), _                                          Split(dMDMs.Item(sKey), ChrW(8203))(1) & Chr(44) & vTMPs(d, 3)), ChrW(8203))        Else            dMDMs.Add Key:=sKey, _                      Item:=Join(Array(CStr(Format(vTMPs(d, 2), '000000000)), Chr(39) & CStr(vTMPs(d, 3))), ChrW(8203))        End If    Next d    'put the dictionary's collated keys and items back into a redimmed vTMPs    'late binding needs to iterate through for each key    Erase vTMPs    ReDim vTMPs(1 To dMDMs.Count, 1 To 4)    d = 1    For Each tmp In dMDMs.Keys        vTMPs(d, 1) = Split(tmp, ChrW(8203))(0)        vTMPs(d, 2) = Split(dMDMs.Item(tmp), ChrW(8203))(0)        vTMPs(d, 3) = Split(dMDMs.Item(tmp), ChrW(8203))(1)        vTMPs(d, 4) = Split(tmp, ChrW(8203))(1)        d = d + 1    Next tmp    'dump all of the temp variant array's values back into the results worksheet    With wsConso        .Cells(2, 1).Resize(UBound(vTMPs, 1), UBound(vTMPs, 2)) = vTMPs    End With    'restore the environment    appTGGLEnd SubSub appTGGL(Optional bTGGL As Boolean = True)    With Application        .ScreenUpdating = bTGGL        .EnableEvents = bTGGL        .DisplayAlerts = bTGGL        .Calculation = IIf(bTGGL, xlCalculationAutomatic, xlCalculationManual)    End With    Debug.Print TimerEnd Sub150,000 rows of random sample data generated to mimic your own sample data (sorted or unsorted). Late Binding: 13.94 seconds Early Binding: 9.48 secondsSample XLSB workbook available temporarily at: MDMDuplicates.xlsbI wasn't too sure on how to deal with the DUNS numbers. Typically for DUNS, I use true numbers formatted as 000000000 or 000000000-0000 (the latter for DUNS+4) as numerical lookups are faster and more versatile than text-based lookups. However, for this I used forced text with leading zeroes to make 9 digit placeholders. The productid numbers were similarly forced into text with a ' Range.PrefixCharacter property so you didn't end up with some as text-that-look-like-numbers (multiple) and some as true numbers (single).I have also retained the country codes and augmented them to the three letter ISO 3166-1 alpha-3 standard. It didn't make sense to have the country codes as one of the unique criteria and then discard them from the results. Setting Require Variable Declaration within the VBE's Tools  Options  Editor property page will put the Option Explicit statement at the top of each newly created code sheet. This will avoid silly coding mistakes like misspellings as well as influencing you to use the correct variable type in the variable declaration. Variables created on-the-fly without declaration are all of the variant/object type. Using Option Explicit is widely considered 'best practice'. If you convert the late binding of the Scripting.Dictionary object to early binding, you must add Microsoft Scripting Runtime to the VBE's Tools  References.  Tests were performed on a five-year-old business class i5 laptop with 8 Gbs of DRAM and Excel 2010 version 14.0.7166.5000 (32-bit). To my mind, that is the low end of the scale on which an operation like this should be performed.