Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Dec 2006
    Posts
    250
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hello, I have a pivot table made up of email addresses and company name. I put the email addresses in the label column as well as company name. I put the email addresses as count in the data field of the pivot table. The reason I am doing this is I want to see how many email addresses are the same. Is there a way I can create a pivot field that will give me only the addresses where the data field is 2 or more? I am attaching a small sample for reference. Thanks.
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Instead of using a pivot table, I'd add a column Count to the data table.
    Formula in C2:

    =COUNTIF($A$2:$A$5,A2)

    and fill down. You can then use AutoFilter to select the rows with Count >= 2.

    See attached version.
    Attached Files Attached Files

  3. #3
    3 Star Lounger
    Join Date
    Dec 2006
    Posts
    250
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks Hans. I got one more question. I need to delete the emails that have two or more. What is the best way of doing this?

  4. #4
    3 Star Lounger
    Join Date
    May 2008
    Location
    India
    Posts
    306
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='Seba' post='785852' date='23-Jul-2009 01:10']Thanks Hans. I got one more question. I need to delete the emails that have two or more. What is the best way of doing this?[/quote]
    If you want to delete all related data having more than 1 mail id, you can use custom auto filter in count & then delete all appeared rows. but this will delete ALL DATA including company name.
    [attachment=84811:untitled.JPG]
    Attached Images Attached Images
    Regards
    Prasad

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    If you filter with condition greater than or equal to 2, you can select the e-mail addresses in column A and press Delete. Only the cells in visible rows will be cleared.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •