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
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" <[email protected]> wrote in message
news:[email protected]...
> 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 <[email protected]>,
"Peter" <[email protected]> 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 <[email protected]>,
> "Peter" <[email protected]> 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" <[email protected]> wrote in message
news:[email protected]...
> 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 <[email protected]>,
> "Peter" <[email protected]> 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 <#[email protected]>,
"Biff" <[email protected]> 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" <[email protected]> wrote in message
news:%[email protected]...
> What's the OR for?
>
> It's doing nothing!
>
> Biff
>
> "Domenic" <[email protected]> wrote in message
> news:[email protected]...
>> 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 <[email protected]>,
>> "Peter" <[email protected]> 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.
>
>
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks