+ Reply to Thread
Results 1 to 8 of 8

Excel - Count distinct occurences and copy unique values

  1. #1
    Registered User
    Join Date
    06-26-2015
    Location
    Seattle, WA
    MS-Off Ver
    2003
    Posts
    4

    Excel - Count distinct occurences and copy unique values

    Hello,

    I am seriously new to MS Excel. What I'm trying to accomplish is:
    Please Login or Register  to view this content.
    So I have the dates column but want to generate the distinct dates (unique dates) and # of occurrences that date is listed.

    Currently using MS Excel 2003 and/or Google Sheets and/or LibreOffice.

    The end product is to generate a line graph.

    Thanks

    btw: this is a xpost from the submission I did over at Stackoverflow.com
    http://stackoverflow.com/questions/3...-unique-values

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    51,807

    Re: Excel - Count distinct occurences and copy unique values

    Try this...
    A
    B
    C
    D
    E
    F
    1
    Dates Distinct dates # of occurrences
    2
    13-Feb-14 13-Feb-14
    8
    3
    13-Feb-14 14-Feb-14
    5
    4
    13-Feb-14
    5
    13-Feb-14
    6
    13-Feb-14
    7
    13-Feb-14
    8
    13-Feb-14
    9
    13-Feb-14
    10
    14-Feb-14
    11
    14-Feb-14
    12
    14-Feb-14
    13
    14-Feb-14
    14
    14-Feb-14

    C2=IFERROR(INDEX($A$2:$A$14,MATCH(0,INDEX(COUNTIF($C$1:C1,$A$2:$A$14),),0)),"")
    copied down

    E2=COUNTIF($A$2:$A$14,C2)
    copied down
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Registered User
    Join Date
    06-26-2015
    Location
    Seattle, WA
    MS-Off Ver
    2003
    Posts
    4

    Re: Excel - Count distinct occurences and copy unique values

    Quote Originally Posted by FDibbins View Post
    Try this...
    A
    B
    C
    D
    E
    F
    1
    Dates Distinct dates # of occurrences
    2
    13-Feb-14 13-Feb-14
    8
    3
    13-Feb-14 14-Feb-14
    5
    4
    13-Feb-14
    5
    13-Feb-14
    6
    13-Feb-14
    7
    13-Feb-14
    8
    13-Feb-14
    9
    13-Feb-14
    10
    14-Feb-14
    11
    14-Feb-14
    12
    14-Feb-14
    13
    14-Feb-14
    14
    14-Feb-14

    C2=IFERROR(INDEX($A$2:$A$14,MATCH(0,INDEX(COUNTIF($C$1:C1,$A$2:$A$14),),0)),"")
    copied down

    E2=COUNTIF($A$2:$A$14,C2)
    copied down
    Oh my goodness... Okay let me get back to you on this one. Need to eat lunch first and have to go to a meeting afterwards

  4. #4
    Registered User
    Join Date
    06-26-2015
    Location
    Seattle, WA
    MS-Off Ver
    2003
    Posts
    4

    Re: Excel - Count distinct occurences and copy unique values

    Quote Originally Posted by random0munky View Post
    Oh my goodness... Okay let me get back to you on this one. Need to eat lunch first and have to go to a meeting afterwards
    Quote Originally Posted by random0munky View Post
    Oh my goodness... Okay let me get back to you on this one. Need to eat lunch first and have to go to a meeting afterwards
    Unfortunately, I wasn't able to get this to work, but it was probably because of user error than the function not working correctly. I actually went the route of using a Pivot Table, adding the dates to the Row and adding the dates again to the Values and using CountA which gave the # of occurrences. Thanks again for the help =)

  5. #5
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Excel - Count distinct occurences and copy unique values

    If you're using Excel 2003 then the IFERROR function is not supported in that version.

    Do you want another formula option or are you satisfied using the pivot table?
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  6. #6
    Registered User
    Join Date
    06-26-2015
    Location
    Seattle, WA
    MS-Off Ver
    2003
    Posts
    4

    Re: Excel - Count distinct occurences and copy unique values

    Quote Originally Posted by Tony Valko View Post
    If you're using Excel 2003 then the IFERROR function is not supported in that version.

    Do you want another formula option or are you satisfied using the pivot table?
    Thanks Tony for reply and offer. I'm fully content with using pivot tables. Thanks

  7. #7
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Excel - Count distinct occurences and copy unique values

    Good deal. Thanks for the feedback!

    If your question has been solved please mark the thread as being solved.

    In the menu bar above the very first post select Thread Tools, then select Mark this thread as solved.

  8. #8
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    51,807

    Re: Excel - Count distinct occurences and copy unique values

    Thanks for the feedback

    Tony, good catch on the 2003/IFERROR, I didnt notice that

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. How to Count Distinct Values in Excel
    By kisanvikas2015 in forum Excel General
    Replies: 2
    Last Post: 05-19-2015, 10:12 AM
  2. [SOLVED] Copy Unique Distinct Values
    By snapfade in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-17-2013, 10:33 AM
  3. Replies: 5
    Last Post: 03-13-2012, 06:05 AM
  4. Generate unique values,count of values occurences then sort
    By Hudas in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-16-2012, 05:22 PM
  5. Count of distinct (unique) values by day
    By velorian in forum Excel General
    Replies: 7
    Last Post: 12-06-2011, 05:03 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1