News, tips, advice, support for Windows, Office, PCs & more. Tech help. No bull. We're community supported by donations from our Plus Members, and proud of it
Home icon Home icon Home icon Email icon RSS icon
  • Counting unique values in a list

    Posted on WSandydan Comment on the AskWoody Lounge
    Viewing 1 reply thread
    • Author
      • #352036 Reply
        AskWoody Lounger

        I have a spreadsheet that contains a list of approximately 17,700 values, but there are only about 100 distinct values and all the rest are duplicates. In other words, the spreadsheet contains one column with 17,700 rows.

        What I would like to do is filter this down to one row for each distinct value, and a count of the number of times that value was in the original list. It doesn’t matter if this summarized data replaces the original list, or is next to it, or on a new sheet, as long as I can get to it.

        Any ideas? I’m using Excel97 on Win95.


      • #510911 Reply
        AskWoody Lounger

        Oh, I just figured it out (after trying for a day or two): All the data was in column A (and it was 19700 rows, not just 17,700), and sorted (I don’t know if that matters, but that’s the way it was).

        I went to Data->Filter->Advanced Filter, and clicked on Copy to another location and on Unique records only, and put the Copy to range in column C.

        Then, I put this formula in Column D:
        and voila! There it was.

        Anyway, thanks for the help I know you would have given if I hadn’t beaten you to it!

    Viewing 1 reply thread

    Please follow the -Lounge Rules- no personal attacks, no swearing, and politics/religion are relegated to the Rants forum.

    Reply To: Counting unique values in a list

    You can use BBCodes to format your content.
    Your account can't use Advanced BBCodes, they will be stripped before saving.