+ Reply to Thread
Results 1 to 5 of 5

count unique occurrence by id

  1. #1
    Registered User
    Join Date
    03-15-2011
    Location
    Iowa City, IA
    MS-Off Ver
    Excel 2013
    Posts
    29

    Question count unique occurrence by id

    Hello All,

    I've looked for the solution for a while and I can't find what I need.

    Here is my Data and Column C is what I want to happen

    A (ID) B (region) C(Count unique occurrence)
    2 metro 2
    2 suburban 2
    3 rural 1
    4 rural 3
    4 metro 3
    4 suburban 3
    5 metro 1
    5 metro 1

    I would really appreciate a formula that allows C to happen.

    Thanks

    Zeda

  2. #2
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,810

    Re: count unique occurrence by id

    Here is a Pivot Table. Will this presentation work for you

    Data Range
    A
    B
    C
    D
    E
    F
    1
    ID
    Region
    2
    2
    metro
    3
    2
    suburban
    ID
    Region
    Count of ID
    4
    3
    rural
    2
    5
    4
    rural
    metro
    1
    6
    4
    metro
    suburban
    1
    7
    4
    suburban
    2 Total
    2
    8
    5
    metro
    3
    9
    5
    metro
    rural
    1
    10
    3 Total
    1
    11
    4
    12
    metro
    1
    13
    rural
    1
    14
    suburban
    1
    15
    4 Total
    3
    16
    5
    17
    metro
    2
    18
    5 Total
    2
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  3. #3
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: count unique occurrence by id

    For a formula solution with data as below try in C2 and fill down.
    Formula: copy to clipboard
    Please Login or Register  to view this content.



    A
    B
    C
    1
    (ID)
    (region)
    (Count unique occurrence)
    2
    2
    metro
    2
    3
    2
    suburban
    2
    4
    3
    rural
    1
    5
    4
    rural
    3
    6
    4
    metro
    3
    7
    4
    suburban
    3
    8
    5
    metro
    1
    9
    5
    metro
    1
    Dave

  4. #4
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: count unique occurrence by id

    You could also try array entering this in C2 and filling down.

    If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    03-15-2011
    Location
    Iowa City, IA
    MS-Off Ver
    Excel 2013
    Posts
    29

    Re: count unique occurrence by id

    Thank you very much. I couldn't do the pivot table because I'm setting the file to be used in a matching analyses and I'm creating helper and aggregate variables. FlameRetired, your formulas are awesome. Thank you so much.

    Zeda

+ 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. Count the first occurrence of a substring
    By trisoldee in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-03-2018, 04:39 PM
  2. [SOLVED] Count Occurrence
    By murray83 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 04-03-2017, 05:13 PM
  3. [SOLVED] Count the number of occurrence upto now and assign a unique sequence number
    By chathuranga in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 12-22-2016, 07:25 AM
  4. [SOLVED] Find unique occurrence of names across a number of columns
    By DPJW in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 12-21-2016, 09:28 AM
  5. Count to next Occurrence (Zero if next)
    By gra0001 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-04-2016, 09:52 PM
  6. [SOLVED] count and sum occurrence against a value
    By koshur in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 11-28-2015, 02:46 AM
  7. Replies: 0
    Last Post: 03-22-2012, 08:44 PM

Tags for this Thread

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