Copy / Filter Data Based On Data In Column Occuring x Times

babadjov · Mar 6th 2008

Ok, Here is the deal, I have a excel worksheet with the following columns:

First name, Last Name, Email address, domain of email, product type, date registered.

The list consists of about 50,000 entries. I want to sort the list by the domain of email(which I am able to do already) Once this is done, I want to find all instances of where a domain appears at least 10 times on the list(such as webmessenger.com appears 40 times, so I want to get that data).

For those instances where the domain appears at least 10 times, I want to pull those rows out of the intial list and put them in a new list(the new list will be sorted by domain and will only have people who have a domain which appears at least 10 times).

To make this a bit more clear, The initial list I have is a list of people who registered to use the software my employer makes. We are trying to locate companies which may have many people using our consumer version of the software. When there is a large amount of people in the same organization using our software, it would benefit them to upgrade to the enterprise version due to enhanced managment features. By running this filter, I can see which companies have at least 10 users registered to use our software. Of course I will remove any Gmail/yahoo mail/msn/hotmail... pretty much any public email domains and just leave the ones that are obviously corporate emails.

So far, I think it probably has to be done with a pivot table... I was able to get a table that tells me how many instances occur from each domain, but I cannot get it to display the actual data(it just says IE. company.com 200, yahoo 120, etc... I need it to show me the 200 rows of company.com emails and extract them to a new sheet so that I can then follow up with company.com and see if they are interested in the corporate version.)

Any help on this would be greatly appreciated. Please leave detailed instructions if you are going to give advice, as I am not an expert in excel. THANKS IN ADVANCE!

Dave Hawley · Mar 7th 2008

Re: Filtering To Extract Rows To New Worksheet

AdvancedFilter with formula criteria like =COUNTIF($A$2:$A$100,A2)>10 where Column A has the Domain names.

Simon Lloyd · Mar 7th 2008

Re: Copy / Filter Data Based On Data In Column Occuring x Times

if you prefer a code version, assuming your data is on sheet2:

Code

Sub Sheets_By_Domain_Over_Ten()
    Dim wSheetStart As Worksheet
    Dim strText As String
    Dim rngSource As Range, rngUnique As Range
    Dim rngSourceLess As Range
     Application.ScreenUpdating = False
    Set wSheetStart = ActiveSheet
    wSheetStart.AutoFilterMode = False
    Set rngSource = wSheetStart.Range(Cells(1, 4), Cells(Cells.Rows.Count, "D").End(xlUp))
    Set rngSourceLess = wSheetStart.Range(Cells(2, 4), Cells(Cells.Rows.Count, "D").End(xlUp))
     
    On Error Resume Next
    Application.DisplayAlerts = False
    Worksheets("UniqueList").Delete
    Worksheets.Add().Name = "UniqueList"
     
    With Worksheets("UniqueList")
         
        rngSource.AdvancedFilter xlFilterCopy, rngSource, .Range("A1"), True
        Set rngUnique = .Range("A2", .Range("A" & .Rows.Count).End(xlUp))
    End With
    On Error Resume Next
    
    On Error GoTo 0
     
    For Each cell In rngUnique
         
        With wSheetStart
        wSheetStart.Activate
        On Error Resume Next
mp = Evaluate(Application.WorksheetFunction.CountIf(Range(Cells(2, 4), Cells(Cells.Rows.Count, "D").End(xlUp)), cell.Value))
            If mp < 10 Then GoTo Nxt
              Worksheets("UniqueList").Range("A1").AutoFilter 1, cell.Value
            Worksheets(cell.Value).Delete
            Worksheets.Add().Name = cell.Value & " Data"
            .Activate
            Cells(1, 4).AutoFilter Field:=4, Criteria1:=cell.Value
            rngSourceLess.SpecialCells(xlCellTypeVisible).EntireRow.Copy _
            Destination:=Worksheets(cell.Value & " Data").Range("A1")
              
            If cell.Value = "" Then Exit For
            .AutoFilterMode = False
            .Activate
        End With
Nxt:
             
    Next cell
     
    wSheetStart.AutoFilterMode = False
    Sheets("UniqueList").Delete
    Application.DisplayAlerts = True
     Application.ScreenUpdating = True
End Sub

Display More

Copy / Filter Data Based On Data In Column Occuring x Times

Participate now!

Share