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

gravanoc

Active Member
Joined
Oct 20, 2015
Messages
346
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: 6

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
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"))
 
Upvote 0
Solution
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.
 
Upvote 0
You're welcome. Thanks for the follow-up. :)
 
Upvote 0

Forum statistics

Threads
1,213,520
Messages
6,114,101
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top