Identify duplicate rows and move them to another excel workbook

I have several excel workbooks with about 30-40 thousand rows of data each sorted by case number. What I need is a way to identify duplicates of the same case number and move them to new workbooks so that each workbook is its own case number. These case numbers have no set range as they are randomly generated if that even matters. I am sure this is possible but do not have the VBA skills nor time to accomplish.
Rob EliAsked:
Who is Participating?
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Please give this a try...

Sub CopyDataToNewWorkbooks()
Dim wb As Workbook
Dim lr As Long, i As Long
Dim x, dict, it
Dim FilePath As String, FileName As String

Application.ScreenUpdating = False
Application.DisplayAlerts = False

FilePath = ThisWorkbook.Path & "\"
lr = Cells(Rows.Count, 1).End(xlUp).Row

If lr < 2 Then
    MsgBox "No data found", vbExclamation
    Exit Sub
End If

x = Range("A2:A" & lr).Value
Set dict = CreateObject("Scripting.Dictionary")

For i = 1 To UBound(x, 1)
    If x(i, 1) <> "" Then
        dict.Item(x(i, 1)) = ""
    End If
Next i
ActiveSheet.AutoFilterMode = False
For Each it In dict.keys
    FileName = it
    With Range("A1").CurrentRegion
        .AutoFilter field:=1, Criteria1:=it
        .SpecialCells(xlCellTypeVisible).Copy
        Set wb = Workbooks.Add
        wb.Sheets(1).Range("A1").PasteSpecial xlPasteAll
        wb.Sheets(1).UsedRange.Columns.AutoFit
        wb.SaveAs FilePath & FileName
        wb.Close True
    End With
Next it
ActiveSheet.AutoFilterMode = False
Application.DisplayAlerts = True
Application.ScreenUpdating = True
MsgBox "Workbooks have been created successfully.", vbInformation
End Sub

Open in new window

The new workbooks will be saved at the location where this macro workbook is saved.
sample.xlsm
0
 
Roy CoxGroup Finance ManagerCommented:
Do you want to this for each individual workbook.

Can you be a little more specific on where the case numbers are stored, i.e. are they in one specific column, if they are in random columns then  the code will be extremely slow.

Perhaps attach an example workbook showing the data layout.
0
 
Mike in ITIT System AdministratorCommented:
As @RoyCox says we need more information.
  • Can you attach a sample workbook with about 10 lines, including some duplicates.
  • How do you want the new workbooks to be named?
  • How many columns are in each row?
  • Which row has the case number?
  • Are the case numbers only numeric? Or are they alphanumeric?
  • Do you want each workbook with the 30 - 40k lines to be parsed individually and produce their own extra workbooks?
  • Or parsed as a whole and make one set of extra workbooks based on duplicates across the main workbooks?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Rob EliAuthor Commented:
The case numbers are the very first column. None of the subsequent columns have case numbers.
0
 
Rob EliAuthor Commented:
Here is a sample guys.
0
 
Rob EliAuthor Commented:
Sorry here.
Sample.xlx.xlsm
0
 
Roy CoxGroup Finance ManagerCommented:
I have some code at home which should do what you want. I've used it for years so I'll amend it for your set up.

Just need to know if you need to do each workbook separately, i.e split each one into separate workbooks. My code will name each new workbook with the Case number
0
 
Rob EliAuthor Commented:
For example in the sample file CASE-14923427 has 6 entries. So from that those six rows will be put in their own workbook named CASE-14923427
0
 
Rob EliAuthor Commented:
Even if it is just one entry I want it in its own workbook. Basically every case number should be a separate workbook.
0
 
aikimarkCommented:
If you have 30k-40k rows, how many unique case values do you have?  You might find distribution to different worksheets becomes unwieldy.

What are you hoping to gain by this distribution?
0
 
Roy CoxGroup Finance ManagerCommented:
Here's my example.

First create a subfolder in the folder that holds the master workbook. In my code I have named it Split Workbooks.

I can add code to auto create the folder if you want
Sample.xlx.xlsm
0
 
Rob EliAuthor Commented:
Subodh Tiwari (Neeraj) and Roy Cox solutions worked. Thanks.
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You're welcome Rob! Glad we could help.
0
 
Roy CoxGroup Finance ManagerCommented:
Pleased to help.
0
 
Roy CoxGroup Finance ManagerCommented:
The points need splitting 50/50 between Subodh Tiwari (Neeraj) and Roy Cox . Not sure how to determine best or assisted, both solutions work well.
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
@Roy
That was simple. The solution posted earlier should have been chosen as the best solution. :)
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
The chosen answer resolved the question.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.