+ Reply to Thread
Results 1 to 8 of 8

Look across values in 12 columns, return the number of unique values

  1. #1
    Registered User
    Join Date
    01-26-2009
    Location
    Kansas, USA
    MS-Off Ver
    Excel 2007
    Posts
    51

    Look across values in 12 columns, return the number of unique values

    Hello ExcelForum,

    I have 12 columns of data. In those 12 columns of data I have 3 digit numeric IDs. I want to count the unique number of IDs for each row. I have about 14K rows.

    What would be the best way to do so?

    Some rules about how the data is stored. I have 12 columns of data with anywhere from 1 to 12 columns having data for each respondent. Data always fills left to right and never skips columns.

    I have attached an example file that represents how the data is stored and the output I would like (Unique Count).

    Thank you everyone in advance.
    Attached Files Attached Files
    Last edited by NukedWhale; 04-21-2009 at 05:17 PM. Reason: Marking solved

  2. #2
    Valued Forum Contributor
    Join Date
    06-16-2006
    Location
    Sydney, Australia
    MS-Off Ver
    2013 64bit
    Posts
    1,394

    Re: Look across values in 12 columns, return the number of unique values

    I am sure someone else will come up with a better solution, possibly using Sumproduct and/or array formulae. But this works. The only downside is that there are hidden columns to do some of the calculations

    Edit: Sorry, it's a dud solution
    Last edited by Mallycat; 04-17-2009 at 07:47 PM. Reason: It didn't work

  3. #3
    Valued Forum Contributor
    Join Date
    06-16-2006
    Location
    Sydney, Australia
    MS-Off Ver
    2013 64bit
    Posts
    1,394

    Re: Look across values in 12 columns, return the number of unique values

    OK, here is a solution using a custom function.

    Basically what it does is
    1. it takes each cell in the range one at a time
    2. it checks if it has been seen before
    3. if it hasn't been seen before, it is unique and it adds one to the count
    4. if it has been seen before, it doesn't count it
  4. it adds the number to a list of numbers already checked for later comparision



Please Login or Register  to view this content.
Attached Files Attached Files
Last edited by Mallycat; 04-17-2009 at 08:40 PM. Reason: Explanation
Reply With QuoteRegister To Reply

  • #4
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Look across values in 12 columns, return the number of unique values

    Formula:

    B2: =SUMPRODUCT(--($C2:$N2<>""),1/COUNTIF($C2:$N2,$C2:$N2&""))
    copy down as required

  • #5
    Registered User
    Join Date
    01-26-2009
    Location
    Kansas, USA
    MS-Off Ver
    Excel 2007
    Posts
    51

    Re: Look across values in 12 columns, return the number of unique values

    Thank you both for your solutions. Can either of you explain how/why DonkeyOte's formula works?

  • #6
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Look across values in 12 columns, return the number of unique values

    Consider the following values in C2:N2

    Please Login or Register  to view this content.
    The Sumproduct:

    Please Login or Register  to view this content.
    will evaluate as follows:

    Please Login or Register  to view this content.
    The Products are thus:

    Please Login or Register  to view this content.
    which when summed: 6 --> {a,b,c,d,f,e}

    Essentially you're taking 1 and dividing by the count of instances of given value in range... thus the sum for all instances of a given value is always 1... each individual instance of a value generates an apportioned value, ie each instance of a is worth 0.25 (1/4), summed those parts will = 1
    Last edited by DonkeyOte; 04-20-2009 at 01:59 PM. Reason: missing double unary in evaluation section

  • #7
    Registered User
    Join Date
    01-26-2009
    Location
    Kansas, USA
    MS-Off Ver
    Excel 2007
    Posts
    51

    Re: Look across values in 12 columns, return the number of unique values

    Again, thank you DonkeyOte. I found a good page on the web discussing sumproduct and powerful ways in which it can be used. I'll read this to understand how your formula works.

    http://www.xldynamic.com/source/xld.SUMPRODUCT.html

    I wonder, is xldynamic run by the same xld that covers vbaexpress?

  • #8
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Look across values in 12 columns, return the number of unique values

    Yes, there, here and MrExcel.com etc... aka Bob Phillips...

    A while back I put together a little Add-in to help generate the matrix output as per Bob's page for Sumproduct formulae as a training aid, it's still available:

    http://www.box.net/shared/zvugsqc5zv

    and an .xls file accompanies it to demonstrate a variety of Sumproducts:

    http://www.box.net/shared/73mg4fdj4t

    It's not the finished article by any means but is useful to illustrate how component parts are evaluated and end result arrived at.

    Bob was going to look at extending the functionality and possibly compiling into an .xll / .dll but I've not heard more from him on that front for a while so it may be he's opted not to pursue.
    Last edited by DonkeyOte; 04-21-2009 at 05:23 PM.

  • + Reply to Thread

    Thread Information

    Users Browsing this Thread

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

    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