+ Reply to Thread
Results 1 to 4 of 4

Listing Unique Observations

  1. #1
    Henrik
    Guest

    Listing Unique Observations

    Hi,

    I am interested in developing a worksheet formula that list each unique
    observation from a dataset as it is dragged down. I am aware that both
    autofilter and pivot tables are capable of doing this, but I want an actual
    worksheet function.

    For instance, I have the following dataset (starting in cell A1):

    Apple
    Banana
    Orange
    Apple
    Apple
    Banana
    Pear
    Mango

    As the function is dragged down (starting with cell C1), it would return the
    following observations (does not have to be in this order):

    Apple
    Banana
    Orange
    Pear
    Mango

    I have previously develoed the following worksheet function to count unique
    observations:

    =SUMPRODUCT((A1:A8<>"")/(COUNTIF(A1:A8,A1:A8)+(A1:A8="")))

    (the function returns 5)

    Your help is greatly appreciated!


    Thanks,
    Henrik

  2. #2
    Peo Sjoblom
    Guest

    Re: Listing Unique Observations

    The easiest way would be to use data>filter>advanced filter, select the
    range and
    check unique records only and copy to another location

    For a formula: in B2 (it cannot start in the first row)

    =INDEX($A$1:$A$8,MATCH(0,COUNTIF($B$1:B1,$A$1:$A$8),0))

    enter it with ctrl + shift & enter and copy down until you get an error

    However I would go with advanced filter

    --
    Regards,

    Peo Sjoblom

    (No private emails please, for everyone's
    benefit keep the discussion in the newsgroup/forum)



    "Henrik" <Henrik@discussions.microsoft.com> wrote in message
    news:5FC29BC2-1EBE-4E16-AF92-6A652B40DCC9@microsoft.com...
    > Hi,
    >
    > I am interested in developing a worksheet formula that list each unique
    > observation from a dataset as it is dragged down. I am aware that both
    > autofilter and pivot tables are capable of doing this, but I want an
    > actual
    > worksheet function.
    >
    > For instance, I have the following dataset (starting in cell A1):
    >
    > Apple
    > Banana
    > Orange
    > Apple
    > Apple
    > Banana
    > Pear
    > Mango
    >
    > As the function is dragged down (starting with cell C1), it would return
    > the
    > following observations (does not have to be in this order):
    >
    > Apple
    > Banana
    > Orange
    > Pear
    > Mango
    >
    > I have previously develoed the following worksheet function to count
    > unique
    > observations:
    >
    > =SUMPRODUCT((A1:A8<>"")/(COUNTIF(A1:A8,A1:A8)+(A1:A8="")))
    >
    > (the function returns 5)
    >
    > Your help is greatly appreciated!
    >
    >
    > Thanks,
    > Henrik




  3. #3
    Domenic
    Guest

    Re: Listing Unique Observations

    Assuming that Column A contains your list...

    B2, copied down:

    =INDEX(A1:$A$8,MATCH(0,COUNTIF($B$1:B1,A1:$A$8),0))

    ....confirmed with CONTROL+SHIFT+ENTER.

    Note that cell B1 should be left empty.

    Hope this helps!

    In article <5FC29BC2-1EBE-4E16-AF92-6A652B40DCC9@microsoft.com>,
    Henrik <Henrik@discussions.microsoft.com> wrote:

    > Hi,
    >
    > I am interested in developing a worksheet formula that list each unique
    > observation from a dataset as it is dragged down. I am aware that both
    > autofilter and pivot tables are capable of doing this, but I want an actual
    > worksheet function.
    >
    > For instance, I have the following dataset (starting in cell A1):
    >
    > Apple
    > Banana
    > Orange
    > Apple
    > Apple
    > Banana
    > Pear
    > Mango
    >
    > As the function is dragged down (starting with cell C1), it would return the
    > following observations (does not have to be in this order):
    >
    > Apple
    > Banana
    > Orange
    > Pear
    > Mango
    >
    > I have previously develoed the following worksheet function to count unique
    > observations:
    >
    > =SUMPRODUCT((A1:A8<>"")/(COUNTIF(A1:A8,A1:A8)+(A1:A8="")))
    >
    > (the function returns 5)
    >
    > Your help is greatly appreciated!
    >
    >
    > Thanks,
    > Henrik


  4. #4
    Aladin Akyurek
    Guest

    Re: Listing Unique Observations

    Insert 2 rows before the current row such that A3:A10 comes house the
    sample you provided.

    In A2 enter: Item (or some such)

    In B2 enter: 0 (This 0 is required.)

    In B3 enter & copy down:

    =IF((A3<>"")*ISNA(MATCH(A3,$A$2:A2,0)),LOOKUP(9.99999999999999E+307,$B$2:B2)+1,"")

    In C1 enter:

    =LOOKUP(9.99999999999999E+307,B:B)

    Note that this gives you a count of unique/distinct items in A.

    In C2 enter: D-List (from Distinct List, or some such)

    In *C3* enter & copy down:

    =IF(ROW()-ROW(C$3)+1<=$C$1,LOOKUP(ROW()-ROW(C$3)+1,B:B,A:A),"")

    Henrik wrote:
    > Hi,
    >
    > I am interested in developing a worksheet formula that list each unique
    > observation from a dataset as it is dragged down. I am aware that both
    > autofilter and pivot tables are capable of doing this, but I want an actual
    > worksheet function.
    >
    > For instance, I have the following dataset (starting in cell A1):
    >
    > Apple
    > Banana
    > Orange
    > Apple
    > Apple
    > Banana
    > Pear
    > Mango
    >
    > As the function is dragged down (starting with cell C1), it would return the
    > following observations (does not have to be in this order):
    >
    > Apple
    > Banana
    > Orange
    > Pear
    > Mango
    >
    > I have previously develoed the following worksheet function to count unique
    > observations:
    >
    > =SUMPRODUCT((A1:A8<>"")/(COUNTIF(A1:A8,A1:A8)+(A1:A8="")))
    >
    > (the function returns 5)
    >
    > Your help is greatly appreciated!
    >
    >
    > Thanks,
    > Henrik


+ 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