G
Guest
I want to extract the unique records from column A into column B by formula
ie: NOT using the Advanced Filter. Any ideas? Thanks.
ie: NOT using the Advanced Filter. Any ideas? Thanks.
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
R
Rowan
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
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
M
Max
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
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
D
Domenic
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!
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!
G
Guest
Many thanks for your contribution guys....I'll try the methods these this
afternoon and post response.
afternoon and post response.
B
Biff
What's the OR for?
It's doing nothing!
Biff
It's doing nothing!
Biff
Domenic said: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!
D
Domenic
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.
records exist. Otherwise the formula would return a #N/A error. It's
something I recently picked up from Harlan.
B