Checking if an e-mail is unique, primary, or secondary in a range

gravanoc

Board Regular
Joined
Oct 20, 2015
Messages
164
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
I have a range containing emails, and I need to decide if each is unique, primary, or secondary. Unique means it is never repeated in the entire list. Primary is repeated, but it is the first occurrence in the list of that e-mail. Secondary is everything else, a repeating e-mail on its 2nd or greater occurrence. I've tried a VBA solution but it has an issue. In the attached picture you can see it almost works correctly, but it labels the final e-mail in a set as unique instead of secondary, and I'm having a bad time trying to fix it. If a formula will do here, that will be fine. Thanks.


VBA Code:
Sub Emails()

    Dim ws1 As Worksheet
    Dim emRng As Range
    Dim emStr() As String
    
    Set ws1 = Sheets("Sheet1")
    Set emRng = ws1.Range("C2:" & ws1.Range("C1048576").End(xlUp).Address)
    
    Application.Calculation = xlCalculationManual
    
    ReDim emStr(0 To emRng.Cells.Count)
    
    For i = 0 To emRng.Cells.Count - 1
    
        For j = 0 To i
            
            If emRng.Cells(i + 1, 1).Value = emStr(j) Then
        
                If emRng.Cells(i - 1, 1).Value <> emStr(j) Then
                
                    emRng.Cells(i, 2).Value = "Primary"
                    Exit For
                    
                End If
                
                emRng.Cells(i, 2).Value = "Secondary"
                
                Exit For
            
        End If
            
        Next
        
        emStr(i) = emRng.Cells(i + 1, 1).Value
        If emRng.Cells(i, 2).Value = "" Then emRng.Cells(i, 2).Value = "Unique"
        
    Next
    
    Application.Calculation = xlCalculationAutomatic

End Sub
 

Attachments

  • ExcelIssue.JPG
    ExcelIssue.JPG
    79.2 KB · Views: 3

Some videos you may like

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,482
Office Version
  1. 365
Platform
  1. Windows
Two suggestions to get better & faster help:
  1. Update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)
  2. Investigate XL2BB for providing sample data to make it easier for helpers by not having to manually type out sample data to test with.

See if this formula does what you want.

20 11 14.xlsm
AB
1
2abc@defPrimary
3ghi@gmail.comUnique
4abc@defSecondary
5tom@homePrimary
6tom@awayUnique
7abc@defSecondary
8abc@defSecondary
9tom@homeSecondary
10ken@abc.comPrimary
11ken@abc.comSecondary
12xyz@abcUnique
Classify Emails
Cell Formulas
RangeFormula
B2:B12B2=IF(COUNTIF(A$2:A$100,A2)=1,"Unique",IF(COUNTIF(A$2:A2,A2)=1,"Primary","Secondary"))
 
Solution

gravanoc

Board Regular
Joined
Oct 20, 2015
Messages
164
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
Two suggestions to get better & faster help:
  1. Update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)
  2. Investigate XL2BB for providing sample data to make it easier for helpers by not having to manually type out sample data to test with.

See if this formula does what you want.

20 11 14.xlsm
AB
1
2abc@defPrimary
3ghi@gmail.comUnique
4abc@defSecondary
5tom@homePrimary
6tom@awayUnique
7abc@defSecondary
8abc@defSecondary
9tom@homeSecondary
10ken@abc.comPrimary
11ken@abc.comSecondary
12xyz@abcUnique
Classify Emails
Cell Formulas
RangeFormula
B2:B12B2=IF(COUNTIF(A$2:A$100,A2)=1,"Unique",IF(COUNTIF(A$2:A2,A2)=1,"Primary","Secondary"))
Thank you, that works perfectly. I will update my account info, I was not aware of the new changes.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,482
Office Version
  1. 365
Platform
  1. Windows
You're welcome. Thanks for the follow-up. :)
 

Watch MrExcel Video

Forum statistics

Threads
1,118,206
Messages
5,570,885
Members
412,345
Latest member
avelraza
Top