[Solved] split excel file into multiple excel files, based on one criteria

IoanaStanescuNN

New member
Hi guys

I have a master file with details about salespersons.
The salespersons email addresses are not unique, so salesperson appears multiple times, with different signed contracts.

I need to create from this file, new excel files to share with every one of them, so they all receive just data about their own contracts.
The new files should keep the header and should be named using the salesperson email addresses.

Pls see some dummy data below.




AgencyUnit_CEmailAgentContractBeneficiuClient informat prin:MaturitateRevendicat
Alba-IuliaAlba Iulia Head Office [email protected]Voucher 400 lei analize barbati SanoPassSMSurmeaza maturitatea contractului
Alba-IuliaAlba Iulia Head Office [email protected]Voucher 400 lei analize hormonale SanoPassEmail+Notificare Pushurmeaza maturitatea contractului
Alba-IuliaAlba Iulia Head Office [email protected]Voucher 400 lei analize hormonale SanoPassEmailurmeaza maturitatea contractului
Alba-IuliaAlba-Iulia Unit Ardeleanu [email protected]Pachet „Energie si Vitalitate" RMEmail
Alba-IuliaAlba-Iulia Unit Ardeleanu [email protected]Voucher 400 lei analize barbati SanoPassEmail+Notificare Push
Alba-IuliaAlba-Iulia Unit Ardeleanu [email protected]Voucher 400 lei analize barbati SanoPassEmail
Alba-IuliaAlba-Iulia Unit Ardeleanu [email protected]Voucher 400 lei analize barbati SanoPassEmail
Alba-IuliaAlba-Iulia Unit Ardeleanu [email protected]Voucher 400 lei analize hormonale SanoPassEmail+Notificare Push
Alba-IuliaAlba-Iulia Unit Ardeleanu [email protected]Voucher 400 lei analize hormonale SanoPassEmail+Notificare Push
Alba-IuliaAlba-Iulia Unit Ardeleanu [email protected]Voucher 400 lei analize hormonale SanoPassEmail
Alba-IuliaAlba-Iulia Unit Ardeleanu [email protected]Voucher 400 lei analize vitamine SanoPassEmail+Notificare Push
Alba-IuliaAlba-Iulia Unit Ardeleanu [email protected]Voucher 400 lei analize vitamine SanoPassEmail
Alba-IuliaAlba-Iulia Unit Ardeleanu [email protected]Voucher 400 lei analize vitamine SanoPassEmail+Notificare Push
Alba-IuliaAlba-Iulia Unit Ardeleanu [email protected]Voucher 400 lei analize vitamine SanoPassEmail
Alba-IuliaAlba-Iulia Unit Ardeleanu [email protected]Voucher 400 lei analize vitamine SanoPassEmail
Alba-IuliaAlba-Iulia Unit Ardeleanu [email protected]Voucher 400 lei analize barbati SanoPassEmail+Notificare Push
Alba-IuliaAlba-Iulia Unit Ardeleanu [email protected]Voucher 400 lei analize vitamine SanoPassEmail
Alba-IuliaAlba-Iulia Unit Ardeleanu [email protected]Voucher 400 lei analize vitamine SanoPassEmail+Notificare Push
Alba-IuliaAlba-Iulia Unit Ardeleanu [email protected]Voucher 400 lei analize vitamine SanoPassEmail+Notificare Push
Alba-IuliaAlba-Iulia Unit Ardeleanu [email protected]Voucher 400 lei analize vitamine SanoPassEmail
Alba-IuliaAlba-Iulia Unit Ardeleanu [email protected]Voucher 400 lei analize vitamine SanoPassEmail
Alba-IuliaAlba-Iulia Unit Ardeleanu [email protected]Voucher 400 lei analize vitamine SanoPassEmail
Alba-IuliaAlba-Iulia Unit Ardeleanu [email protected]Voucher 400 lei analize vitamine SanoPassEmailurmeaza maturitatea contractului
Alba-IuliaAlba-Iulia Unit Ardeleanu [email protected]Pachet „Energie si Vitalitate" RMSMS
Alba-IuliaDeva Unit Bohateret [email protected]Pachet analize “Vitalitate Masculina” RMEmail+Notificare Push
Alba-IuliaDeva Unit Bohateret [email protected]Pachet analize “Vitalitate Masculina” RMEmail+Notificare Push
Alba-IuliaDeva Unit Bohateret [email protected]Voucher 400 lei analize barbati SanoPassEmail+Notificare Push
[td]
1​
[/td]​
[td]
2​
[/td]​
[td]
3​
[/td]​
[td]
4​
[/td]​
[td]
5​
[/td]​
[td]
6​
[/td]​
[td]
7​
[/td]​
[td]
8​
[/td]​
[td]
9​
[/td]​
[td]
10​
[/td]​
[td]
11​
[/td]​
[td]
12​
[/td]​
[td]
13​
[/td]​
[td]
14​
[/td]​
[td]
15​
[/td]​
[td]
16​
[/td]​
[td]
17​
[/td]​
[td]
18​
[/td]​
[td]
19​
[/td]​
[td]
20​
[/td]​
[td]
21​
[/td]​
[td]
22​
[/td]​
[td]
23​
[/td]​
[td]
24​
[/td]​
[td]
25​
[/td]​
[td]
26​
[/td]​
[td]
27​
[/td]​




So I need to get to smth like this, an excel for each email, named "[email protected]" in this case, with this format:


AgencyUnit_CEmailAgentContractBeneficiuClient informat prin:MaturitateRevendicat
Alba-IuliaAlba Iulia Head Office [email protected]Voucher 400 lei analize barbati SanoPassSMSurmeaza maturitatea contractului
Alba-IuliaAlba Iulia Head Office [email protected]Voucher 400 lei analize hormonale SanoPassEmail+Notificare Pushurmeaza maturitatea contractului
Alba-IuliaAlba Iulia Head Office [email protected]Voucher 400 lei analize hormonale SanoPassEmailurmeaza maturitatea contractului
[td]
1​
[/td]​
[td]
2​
[/td]​
[td]
3​
[/td]​



Do you think you can help me out here?

Thanks so much!
 

Attachments

Hello IoanaStanescuNN

To accomplish the task of splitting the master file into multiple Excel files based on the salesperson's email address, you can use Excel VBA to automate this process. Here's a simple VBA code to help you achieve this:

  • Press Alt + F11 to open the VBA editor.
  • Insert a new module by clicking Insert >> Module.
  • Paste the following code into the module:
Code:
Sub SplitByEmail()
    Dim ws As Worksheet
    Dim wsNew As Worksheet
    Dim emailCol As Range
    Dim emailCell As Range
    Dim lastRow As Long
    Dim uniqueEmails As Collection
    Dim email As Variant
    Dim emailRange As Range
    Dim newWorkbook As Workbook
    Dim filename As String
    
    ' Set the worksheet and email column (assume EmailAgent in Column C)
    Set ws = ThisWorkbook.Sheets(1)
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    Set emailCol = ws.Range("C2:C" & lastRow) ' Adjust this range if needed
    
    ' Create a collection to store unique email addresses
    Set uniqueEmails = New Collection
    
    ' Loop through email column and add unique email addresses to the collection
    On Error Resume Next ' Ignore errors for duplicate keys
    For Each emailCell In emailCol
        uniqueEmails.Add emailCell.Value, CStr(emailCell.Value)
    Next emailCell
    On Error GoTo 0 ' Turn error handling back on
    
    ' Loop through each unique email and create new workbooks
    For Each email In uniqueEmails
        ' Create a new workbook and copy the header
        Set newWorkbook = Workbooks.Add
        Set wsNew = newWorkbook.Sheets(1)
        ws.Rows(1).Copy Destination:=wsNew.Rows(1)
        
        ' Copy rows with the current email to the new workbook
        For Each emailRange In emailCol
            If emailRange.Value = email Then
                ws.Rows(emailRange.Row).Copy Destination:=wsNew.Rows(wsNew.Cells(wsNew.Rows.Count, "A").End(xlUp).Row + 1)
            End If
        Next emailRange
        
        ' Save the new workbook with the email address as the filename
        filename = "C:\YourPath\" & email & ".xlsx" ' Adjust path as necessary
        newWorkbook.SaveAs filename
        newWorkbook.Close SaveChanges:=False
    Next email
    
    MsgBox "Files created successfully!"
End Sub

  • Modify the path "C:\YourPath\" in the filename variable to where you want to save the files.
  • Ensure the column references are correct (Column C for EmailAgent in this case).
  • This will create separate Excel files for each unique salesperson email, copying only their relevant rows and keeping the headers in each file.
 
Hello IoanaStanescuNN

To accomplish the task of splitting the master file into multiple Excel files based on the salesperson's email address, you can use Excel VBA to automate this process. Here's a simple VBA code to help you achieve this:

  • Press Alt + F11 to open the VBA editor.
  • Insert a new module by clicking Insert >> Module.
  • Paste the following code into the module:
Code:
Sub SplitByEmail()
    Dim ws As Worksheet
    Dim wsNew As Worksheet
    Dim emailCol As Range
    Dim emailCell As Range
    Dim lastRow As Long
    Dim uniqueEmails As Collection
    Dim email As Variant
    Dim emailRange As Range
    Dim newWorkbook As Workbook
    Dim filename As String
   
    ' Set the worksheet and email column (assume EmailAgent in Column C)
    Set ws = ThisWorkbook.Sheets(1)
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    Set emailCol = ws.Range("C2:C" & lastRow) ' Adjust this range if needed
   
    ' Create a collection to store unique email addresses
    Set uniqueEmails = New Collection
   
    ' Loop through email column and add unique email addresses to the collection
    On Error Resume Next ' Ignore errors for duplicate keys
    For Each emailCell In emailCol
        uniqueEmails.Add emailCell.Value, CStr(emailCell.Value)
    Next emailCell
    On Error GoTo 0 ' Turn error handling back on
   
    ' Loop through each unique email and create new workbooks
    For Each email In uniqueEmails
        ' Create a new workbook and copy the header
        Set newWorkbook = Workbooks.Add
        Set wsNew = newWorkbook.Sheets(1)
        ws.Rows(1).Copy Destination:=wsNew.Rows(1)
       
        ' Copy rows with the current email to the new workbook
        For Each emailRange In emailCol
            If emailRange.Value = email Then
                ws.Rows(emailRange.Row).Copy Destination:=wsNew.Rows(wsNew.Cells(wsNew.Rows.Count, "A").End(xlUp).Row + 1)
            End If
        Next emailRange
       
        ' Save the new workbook with the email address as the filename
        filename = "C:\YourPath\" & email & ".xlsx" ' Adjust path as necessary
        newWorkbook.SaveAs filename
        newWorkbook.Close SaveChanges:=False
    Next email
   
    MsgBox "Files created successfully!"
End Sub

  • Modify the path "C:\YourPath\" in the filename variable to where you want to save the files.
  • Ensure the column references are correct (Column C for EmailAgent in this case).
  • This will create separate Excel files for each unique salesperson email, copying only their relevant rows and keeping the headers in each file.
This is amazing!
Works perfect!

Thanks so much for your help and time!
 

Online statistics

Members online
0
Guests online
12
Total visitors
12

Forum statistics

Threads
371
Messages
1,623
Members
704
Latest member
Michael Mpofu
Back
Top