Hi, is there a way of returning the list of unique records from a list of
data. For example, my list could contain 1, 1, 2, 3, 1, 2, 9, 1, 4, 5, 4, 4,
3, 2, 3, etc... I know I can use the frequency function but my list has over
2000 entries and I dont know what the actual unique list of numbers is in
order to use the frequency function. I could also use a pivot table but was
hoping to use some formula or formulae.

Hope this makes sense

Thanks.

Hi,
suppose that your list start in C1 in D1 enter

=IF(COUNTIF($C$1:C1,C1)=1,C1,"")

copy formula down

"Ronan" wrote:

Hi, is there a way of returning the list of unique records from a list of
data. For example, my list could contain 1, 1, 2, 3, 1, 2, 9, 1, 4, 5, 4, 4,
3, 2, 3, etc... I know I can use the frequency function but my list has over
2000 entries and I dont know what the actual unique list of numbers is in
order to use the frequency function. I could also use a pivot table but was
hoping to use some formula or formulae.

Hope this makes sense

Thanks.

If your data is in column B, then in A1 enter
1
and in A2 enter:
=IF(COUNTIF(B$1:B2,B2)1,"",1+MAX(A$1:A1)) and copy down. For exmple:

1 Curley
Curley
Curley
Curley
2 Ignatz
Ignatz
Ignatz
Ignatz
Ignatz
Ignatz
Ignatz
3 Larry
Larry
Larry
Larry
Larry
4 Moe
Moe
Moe
Moe
Moe
5 Shepp
Shepp
Shepp
Shepp
Shepp
Shepp
Shepp
Shepp
Shepp
Then in D1 enter:
=IF(ISERROR(VLOOKUP(ROW(),A1:B30,2,FALSE)),"",VLOO KUP(ROW(),A1:B30,2,FALSE))
and copy down

This will display:


Curley
Ignatz
Larry
Moe
Shepp

--
Gary''s Student - gsnu200909


"Ronan" wrote:

Hi, is there a way of returning the list of unique records from a list of
data. For example, my list could contain 1, 1, 2, 3, 1, 2, 9, 1, 4, 5, 4, 4,
3, 2, 3, etc... I know I can use the frequency function but my list has over
2000 entries and I dont know what the actual unique list of numbers is in
order to use the frequency function. I could also use a pivot table but was
hoping to use some formula or formulae.

Hope this makes sense

Thanks.

The easiest way to extract uniques is by using the advanced filter. See
this:

http://contextures.com/xladvfilter01.html#FilterUR

--
Biff
Microsoft Excel MVP


"Ronan" wrote in message
...
Hi, is there a way of returning the list of unique records from a list of
data. For example, my list could contain 1, 1, 2, 3, 1, 2, 9, 1, 4, 5, 4,
4,
3, 2, 3, etc... I know I can use the frequency function but my list has
over
2000 entries and I dont know what the actual unique list of numbers is in
order to use the frequency function. I could also use a pivot table but
was
hoping to use some formula or formulae.

Hope this makes sense

Thanks.