Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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
|
|||
|
|||
The easiest way would be to use datafilteradvanced 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" wrote in message ... 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
|
|||
|
|||
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 , 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 |
#4
|
|||
|
|||
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.99 999999999999E+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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Finding Unique Values in Column | Excel Discussion (Misc queries) | |||
find rows for unique data in 1 column and different data in other. | Excel Discussion (Misc queries) | |||
How do I count or display unique data in a column? | Excel Worksheet Functions | |||
Count of unique items meeting condition | Excel Worksheet Functions | |||
UNIQUE | Excel Worksheet Functions |