+ Reply to Thread
Results 1 to 13 of 13

Count unique values in list but NOT using ARRAY formula

  1. #1
    Registered User
    Join Date
    02-24-2013
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2007
    Posts
    8

    Count unique values in list but NOT using ARRAY formula

    http://www.excelforum.com/excel-form...ir-status.html

    Same problem as the solved one above but MUST NOT use array formula.

    1. WITHOUT using array formula
    AND
    2. ONLY using formula, no menu sort or macro options

    ie. columnn A has list of numbers, some occur more than once.
    output to two new columns listing each unique number and how often it occurs in first column.

    This could instead be names instead of numbers.

    If I use countifs or frequency i would still get duplicated counts.

    Note: the list length may be a fixed/defined length. eg. Out of 12 values, count the unique values.
    A B C
    MARY MARY 3
    JOHN JOHN 2
    JOHN JOE 1
    MARY
    JOE
    MARY

  2. #2
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: Count unique values in list but NOT using ARRAY formula

    Hi abc,

    Better use a pivot table, see attached:-

    Count Uniques.xlsx

    Regards,
    DILIPandey
    <click on below * if this helps>
    DILIPandey
    +91 9810929744
    dilipandey@gmail.com

  3. #3
    Forum Moderator Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,745

    Re: Count unique values in list but NOT using ARRAY formula

    If you need to do this ONLY using formulas, without to use ARRAY formulas, then we have to use helper column(s).

    Or else maybe a Pivot table OR Use the Advanced Filter.
    Regards

    Fotis.

    -This is my Greek whisper to Europe.

    --Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.

    Advanced Excel Techniques: http://excelxor.com/

    --KISS(Keep it simple Stupid)

    --Bring them back.

    ---See about Acropolis of Athens.

    --Visit Greece.

  4. #4
    Registered User
    Join Date
    02-24-2013
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2007
    Posts
    8

    Re: Count unique values in list but NOT using ARRAY formula

    Thanks, can't use Pivot tables or filters either. Formula only.

    This is for converting to web page via Spreadsheet Converter.

    Will investigate helper columns. At least the qty of items to assess is fixed.

  5. #5
    Forum Moderator Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,745

    Re: Count unique values in list but NOT using ARRAY formula

    Sorry but i don't understand. Do you want to use helper-hidden columns for this or NO?

  6. #6
    Registered User
    Join Date
    03-13-2013
    Location
    dhaka, bangladesh
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: Count unique values in list but NOT using ARRAY formula

    If(countif($a$1:a1,a1)>1,"",a1)
    Last edited by taifur_rahman; 03-14-2013 at 04:59 AM.

  7. #7
    Registered User
    Join Date
    02-24-2013
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2007
    Posts
    8

    Re: Count unique values in list but NOT using ARRAY formula

    Quote Originally Posted by Fotis1991 View Post
    Sorry but i don't understand. Do you want to use helper-hidden columns for this or NO?
    That probably would be OK for the converter app.

    Just looking at Helper columns now http://www.youtube.com/watch?v=3tfwyD-T7Zs
    Spreadsheet converter supports many (many) of the formula (converts functionality to javascript) but cannot do aforementioned or much/anything to do with arrays.

  8. #8
    Registered User
    Join Date
    02-24-2013
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2007
    Posts
    8

    Re: Count unique values in list but NOT using ARRAY formula

    Sample of data and progress so far to sort.
    Attached Files Attached Files

  9. #9
    Forum Moderator Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,745

    Re: Count unique values in list but NOT using ARRAY formula

    See my example for what i mean "Solution using helper columns"
    Attached Files Attached Files

  10. #10
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,674

    Re: Count unique values in list but NOT using ARRAY formula

    What do you mean "without using array formulas"? If you mean formulas without CTRL+SHIFT+ENTER then give this a go.....

    Assumes data in A1:A10 (can include blanks, numbers or text)

    In C1 use this formula

    =IF(A1="","",A1)

    in C2 this formula copied to C10

    =IFERROR(INDEX(A$1:A$10,MATCH(1,INDEX((COUNTIF(C$1:C1,A$1:A$10&"")=0)*(A$1:A$10<>""),0),0)),"")

    and in D1 copied to D10

    =IF(C1="","",COUNTIF(A$1:A$10,C1))
    Attached Files Attached Files
    Audere est facere

  11. #11
    Registered User
    Join Date
    02-24-2013
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2007
    Posts
    8

    Re: Count unique values in list but NOT using ARRAY formula

    Quote Originally Posted by taifur_rahman View Post
    If(countif($a$1:a1,a1)>1,"",a1)
    That's helped heaps!

    For spreadsheet converter compatibility, had to replace "" values with 0 - zero numerical values.

    I've added a column to count the above unique list.

    Now just need to remove unwanted rows.

    Attached solves the original problem, except for the rows with zeroes.
    Attached Files Attached Files

  12. #12
    Registered User
    Join Date
    02-24-2013
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2007
    Posts
    8

    Re: Count unique values in list but NOT using ARRAY formula

    Quote Originally Posted by daddylonglegs View Post
    What do you mean "without using array formulas"? If you mean formulas without CTRL+SHIFT+ENTER then give this a go.....
    Hi, yes, non-"CSE" array formula. I've tested this and it indeed works fine within Excel, however Spreadsheet converter fails with the warning that array formulas only sparsely supported.

    It fails on the column C formula.

    I've otherwise solved it with the hint of the earlier posting (and provided a means of sorting both result columns)!.

    "solved" as in acceptable by Spreadsheet Converter.

    Preparing a post to close the thread. Once again, thanks.

  13. #13
    Registered User
    Join Date
    02-24-2013
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2007
    Posts
    8

    Re: Count unique values in list but NOT using ARRAY formula

    Solved with aspects of suggested solutions above.

    Taufur Rahman's elegant code started the ball rolling.

    list2.xlsx

    Thanks to all contributors, although the solutions ran into issues with Spreadsheet converter.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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