Display a list of unique values from a column with multiple instances

[jaggy]

Hi-

I have a list in a column (column A) that has many duplicate values. I
am looking to create a list of the unique values from column A into
another column (column F). I just want to display the values from col
A into col F without deleting any values from col A. From there I am
planning on doing a countif based on the value in col F to see how
many duplicates I have.

I want to do this dynamically because anyone can add a new value in
col A but I don't want to have to manually compare unique values in
col A against col F to do the counts.

Col A contains ID numbers that can be repeated many times.
Col F will display all unique values from col A
Col G will do a countif using the value in col F against the data in
col A.

How do I go about doing this?

Thanks in advance,
jag

 

[Don Guillett]

data>filter>advanced filter>unique>copy to another location f1

 

[jaggy]

data>filter>advanced filter>unique>copy to another location f1

Thanks for the reply. Are the other ways of doing this dynamically?

jag

 

[Max]

.. Are the other ways of doing this dynamically?

Here's a set-up which will achieve it

Source data assumed entered in A2 down
In E2: =IF(A2="","",IF(COUNTIF(A$2:A2,A2)>1,"",ROW()))
Leave E1 empty

In F2:
=IF(ROWS($1:1)>COUNT(E:E),"",INDEX(A:A,SMALL(E:E,ROWS($1:1))))
Copy E2:F2 down to cover the max expected extent of data in col A, eg down
to F200? Minimize/hide away col E. Col F will return the dynamic list of
uniques in col A.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:25,000 Files:300 Subscribers:70
xdemechanik
---

 

[Ken Johnson]

Thanks for the reply.  Are the other ways of doing this dynamically?

Here's one way using a helper column...

=IF(COUNTIF(A$2:A2,A2)=1,ROW(1:1),"")

in the helper column (if your data doesn't start in row 2 then change
all the the three 2s to suit).

=IF(ISERROR(SMALL(helper_column_address_here,ROW(1:1))),"",(INDEX($A:
$A,MATCH(SMALL(helper_column_address_here,ROW
(1:1)),helper_column_address_here,0))))

You need to ensure that the formula in the helper column is filled
down at least as far as column A.

Use =SUMPRODUCT(--($F$2:$F$65536<>"")) to count the unique values.

If by "list" you are referring to the excel List feature then use...

=SUMPRODUCT(--(INDIRECT(H1)<>"")) where H1 contains the text value $F
$2:$F$65536

Ken Johnson