Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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. |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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. |
#5
|
|||
|
|||
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. |
#6
|
|||
|
|||
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. |
#7
|
|||
|
|||
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 |
#8
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Modifying a Formula To display only Unique Values | Excel Worksheet Functions | |||
Find Matching Records in Two Worksheets | Excel Discussion (Misc queries) | |||
IF & VLOOKUP FORMULA | Excel Worksheet Functions | |||
In column A I have duplicate records. How do I tag an unique reco. | Excel Discussion (Misc queries) | |||
Help with macro formula and variable | Excel Worksheet Functions |