EXCEL 2007:  Counting Repetitive Data in Cells

Posted on 2011-10-20
Last Modified: 2012-05-12
Hello Experts!

    I have a spreadsheet that consists of a few columns.  One of my columns, the "Account" column I've done a "Sort By" on.  I've noticed I have repetitive entries in that column that I'm trying to count. It wouldn't be that bad but this worksheet is rather long so I'm having to manually label each of the same entries (1), (2), (3) and this is what is taking so long.  Is there a formula I could use that might make my job a little easier?  I've attached a sample of what I'm describing.  Any help with this is GREATLY APPRECIATED!

Excel Sample
Question by:itsmevic
    LVL 50

    Expert Comment

    by:barry houdini
    I'm not quite clear what you need to do? The Accounts you show in the example are all different aren't they? Are you saying there are a total of 8 USER5s in the data, 5 USER10s etc?

    If so perhaps use a new columns with a formula like this in F2

    =E2&" ("&COUNTIF(E:E,E2)&")"

    that will put the Account name with the occurrences in brackets

    regards, barry
    LVL 14

    Accepted Solution

    Your best bet would be to create a pivot table.  If you need guidance doing that, let us know.  
    LVL 50

    Expert Comment

    by:barry houdini
    Sorry, I think I understand now - my formula lableed each occurence with the total number, if you want them numbered 1, 2, 3 etc then modify to this version

    =E2&" ("&COUNTIF(E$2:E2,E2)&")"

    regards, barry
    LVL 50

    Assisted Solution

    by:barry houdini
    Here's an example


    Author Closing Comment

    Both solutions worked great, however the pivot table seemed to help better in this particular case.  Thank you both very much for your input.

    Featured Post

    Free Trending Threat Insights Every Day

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Join & Write a Comment

    This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
    My experience with Windows 10 over a one year period and suggestions for smooth operation
    This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
    Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

    729 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now