Extract duplicates

[Mikelowrey]

Hello,

I have around 80K entries, what I would like to do is find the duplicates and extract them to a different document. how can I achieve that?

Thank you.

 

[SteveO59L]

what do you want to extract?

all values that have duplicates?
just the duplicated values?

 

[Yongle]

You were not very specific in your requirements, so this is a bit of a guess
- if it's not what you want, then let us know

VBA below looks at column A in sheet named "Duplicates" and places a duplicated value ONCE in a new worbook

Place the code in a standard module and save the workbook before running the code
New workbook is saved to same folder
Amend as required

Sub CopyDuplicates()
    Application.ScreenUpdating = False
    Dim a, r As Range, coll As New Collection, i As Long
    Dim wb As Workbook, ws As Worksheet, wPath As String, wName As String, wFullName As String
'where is the data, where to save results
    Set ws = ThisWorkbook.Sheets("Duplicates")
    wPath = ThisWorkbook.Path
    wName = "ExtractedDuplicates " & Format(Now, "yy mm dd hh mm")
    wFullName = wPath & "\" & wName
    Set r = ws.Range("A2", Range("A" & Rows.Count).End(xlUp))
'put values from column A in array
    a = r.Value
'put unique values in collection
    On Error Resume Next
    For i = 1 To UBound(a)
        If WorksheetFunction.CountIf(r, a(i, 1)) > 1 Then coll.Add a(i, 1), a(i, 1)
    Next
    On Error GoTo 0
'add workbook
    Set wb = Workbooks.Add
'place unique values in column A
    With wb.Sheets(1)
        .Cells(1, 1) = "Values"
            For i = 1 To coll.Count
                .Cells(i + 1, 1) = coll(i)
            Next
    End With
'save and close
    wb.SaveAs (wFullName)
    wb.Close (True)
    MsgBox wFullName & " created" & vbCr & coll.Count & " duplicates", vbOKCancel, "INFO"
    Application.ScreenUpdating = True
End Sub

 

[Mikelowrey]

what do you want to extract?

all values that have duplicates?
just the duplicated values?

Just the duplicated values

 

[Mikelowrey]

You were not very specific in your requirements, so this is a bit of a guess
- if it's not what you want, then let us know

VBA below looks at column A in sheet named "Duplicates" and places a duplicated value ONCE in a new worbook

Place the code in a standard module and save the workbook before running the code
New workbook is saved to same folder
Amend as required

Sub CopyDuplicates()
    Application.ScreenUpdating = False
    Dim a, r As Range, coll As New Collection, i As Long
    Dim wb As Workbook, ws As Worksheet, wPath As String, wName As String, wFullName As String
'where is the data, where to save results
    Set ws = ThisWorkbook.Sheets("Duplicates")
    wPath = ThisWorkbook.Path
    wName = "ExtractedDuplicates " & Format(Now, "yy mm dd hh mm")
    wFullName = wPath & "\" & wName
    Set r = ws.Range("A2", Range("A" & Rows.Count).End(xlUp))
'put values from column A in array
    a = r.Value
'put unique values in collection
    On Error Resume Next
    For i = 1 To UBound(a)
        If WorksheetFunction.CountIf(r, a(i, 1)) > 1 Then coll.Add a(i, 1), a(i, 1)
    Next
    On Error GoTo 0
'add workbook
    Set wb = Workbooks.Add
'place unique values in column A
    With wb.Sheets(1)
        .Cells(1, 1) = "Values"
            For i = 1 To coll.Count
                .Cells(i + 1, 1) = coll(i)
            Next
    End With
'save and close
    wb.SaveAs (wFullName)
    wb.Close (True)
    MsgBox wFullName & " created" & vbCr & coll.Count & " duplicates", vbOKCancel, "INFO"
    Application.ScreenUpdating = True
End Sub

Wow, very technical for me, trying to figure this out. Let me show you. This is the document: https://bit.ly/2qCahqn from that document, some of the companies repeats themselves, I would like to be able to take the duplicates from that workbook and move it to another one so I can see a total of duplicates. Hopes this is clear enough

 

[Rick Rothstein]

Wow, very technical for me, trying to figure this out. Let me show you. This is the document: https://bit.ly/2qCahqn from that document, some of the companies repeats themselves, I would like to be able to take the duplicates from that workbook and move it to another one so I can see a total of duplicates. Hopes this is clear enough

What you ultimately want is still not clear to me...

Do you want just list of company names that appear more than once?

Or do you want the entire row of data for all company names that appear more than once?

Or do you want the entire row of data only for when the entire row of data is duplicated?

You thread title says "Extract Duplicates"... does that mean delete them from the original list as well?

 

[Mikelowrey]

What you ultimately want is still not clear to me...

Do you want just list of company names that appear more than once?

Or do you want the entire row of data for all company names that appear more than once?

Or do you want the entire row of data only for when the entire row of data is duplicated?

You thread title says "Extract Duplicates"... does that mean delete them from the original list as well?

Hello, thanks for the quick reply, what Im really trying to figure out is if there is a way to see how many duplicates each company has and move those duplicates to a new workbook, the main difference between duplicates is the license type mostly (I believe). And then form there I'm trying to get an accurate count of the whole list.

 

[Yongle]

@Mikelowrey - there is no need to quote complete posts
It is sometimes useful to quote bits of earlier posts to clarify what you are referring to (especially if there are several subsequent posts). Or else aim your comment @someone (as I have done here).
The code will not change much but i will not amend anything in it until I fully understand..
- which column(s) should appear in the new workbook
- what constitutes a duplicate value (the whole row or a single cell being identical)
- what happens in the original workbook - delete the duplicates or leave them untouched?

 

[Mikelowrey]

@Yongle
- column A to F are unique numbers assigned to them but in the license name if it says the same name then I would take one of those duplicates and extract the whole row to a new workbook.
- If in column G the name are the same, I would consider that a duplicate
-If we can remove the duplicates from the original workbook it would allow me to get an accurate amount as I am trying to figure out the total of License business. so delete.

Hope this is clear enough, if anything, let me know

 

[SteveO59L]

try posting a sample of your data, and what you would like to do with it