Results 1 to 2 of 2
  1. #1
    Lounger
    Join Date
    Jan 2001
    Location
    Tucson, Arizona, USA
    Posts
    45
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Counting unique values in a list

    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.

    Thanks!

  2. #2
    Lounger
    Join Date
    Jan 2001
    Location
    Tucson, Arizona, USA
    Posts
    45
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Counting unique values in a list

    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:
    =COUNTIF($A$2:$A$19770,C2)
    and voila! There it was.

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

Posting Permissions

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