Extracting unique records by formula
 

I want to extract the unique records from column A into column B by formula
ie: NOT using the Advanced Filter. Any ideas? Thanks.
--
Peter
London, UK

Stolen from J-Walk:
Assuming data is in cells A2:A100. Enter this array formula in B2 -
commit with ctrl+shift+enter.
=INDEX($A$2:$A$100,SMALL(IF(MATCH($A$2:$A$100,$A$2 :$A$100,0)=ROW($A$1:INDIRECT(CHAR(65)&COUNTA($A$2: $A$100))),MATCH($A$2:$A$100,$A$2:$A$100,0),""),ROW ()-ROW($A$2:$A$100)+1))
Copy down.
Note you will have to adjust the range to suit your data as any blank
cells will result in a #NA error.

Hope this helps
Rowan

Peter wrote:
I want to extract the unique records from column A into column B by formula
ie: NOT using the Advanced Filter. Any ideas? Thanks.

Another play to extract the uniques list using non-array formulas

Assuming records listed in A1 down

Put in B1:
=IF(A1="","",IF(COUNTIF($A$1:A1,A1)1,"",ROW()))

Put in C1:
=IF(ISERROR(SMALL(B:B,ROWS($A$1:A1))),"",
INDEX(A:A,MATCH(SMALL(B:B,ROWS($A$1:A1)),B:B,0)))

Select B1:C1, copy down till the last row of data in col A

Col C will return the list of unique records in col A,
neatly bunched at the top
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Peter" wrote in message
...
I want to extract the unique records from column A into column B by
formula
ie: NOT using the Advanced Filter. Any ideas? Thanks.
--
Peter
London, UK

Assuming that A1:A10 contains your data, try the following...

B1: Leave empty

B2, copied down:

=IF(OR(COUNTIF($B$1:B1,A1:$A$10)=0),INDEX(A1:$A$10 ,MATCH(0,COUNTIF($B$1:B
1,A1:$A$10),0)),"")

....confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

Hope this helps!

In article ,
"Peter" wrote:

I want to extract the unique records from column A into column B by formula
ie: NOT using the Advanced Filter. Any ideas? Thanks.

Many thanks for your contribution guys....I'll try the methods these this
afternoon and post response.
--
Peter
London, UK


"Domenic" wrote:

Assuming that A1:A10 contains your data, try the following...

B1: Leave empty

B2, copied down:

=IF(OR(COUNTIF($B$1:B1,A1:$A$10)=0),INDEX(A1:$A$10 ,MATCH(0,COUNTIF($B$1:B
1,A1:$A$10),0)),"")

....confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

Hope this helps!

In article ,
"Peter" wrote:

I want to extract the unique records from column A into column B by formula
ie: NOT using the Advanced Filter. Any ideas? Thanks.

What's the OR for?

It's doing nothing!

Biff

"Domenic" wrote in message
...
Assuming that A1:A10 contains your data, try the following...

B1: Leave empty

B2, copied down:

=IF(OR(COUNTIF($B$1:B1,A1:$A$10)=0),INDEX(A1:$A$10 ,MATCH(0,COUNTIF($B$1:B
1,A1:$A$10),0)),"")

...confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

Hope this helps!

In article ,
"Peter" wrote:

I want to extract the unique records from column A into column B by
formula
ie: NOT using the Advanced Filter. Any ideas? Thanks.

Actually, it's there so that the cell remains blank when no more unique
records exist. Otherwise the formula would return a #N/A error. It's
something I recently picked up from Harlan.

In article ,
"Biff" wrote:

What's the OR for?

It's doing nothing!

Biff

What's the OR for?

It's doing nothing!

Nevermind, I see!

Biff

"Biff" wrote in message
...
What's the OR for?

It's doing nothing!

Biff

"Domenic" wrote in message
...
Assuming that A1:A10 contains your data, try the following...

B1: Leave empty

B2, copied down:

=IF(OR(COUNTIF($B$1:B1,A1:$A$10)=0),INDEX(A1:$A$10 ,MATCH(0,COUNTIF($B$1:B
1,A1:$A$10),0)),"")

...confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

Hope this helps!

In article ,
"Peter" wrote:

I want to extract the unique records from column A into column B by
formula
ie: NOT using the Advanced Filter. Any ideas? Thanks.