Home / Excel Tips / Remove Duplicates or Create a List of Unique Records using Advanced Data Filter

Remove Duplicates or Create a List of Unique Records using Advanced Data Filter

 
Related Links:
Count Number of Unique Values, with Excel Functions
Remove Duplicates or Create a List of Unique Records using Excel Formula
Remove Duplicates in a range, using “Remove duplicates” button in Data Tools
 
 
Create a List of Unique Records Using Advanced Data Filter:
——————————————————————————————————–
Using Advanced Data Filter
 
Table 1
Select a cell in the data range
 
In Excel 97 to 2003: Data  ->  Filter  ->  Advanced Filter; In Excel 2007: Data  ->  Sort & Filter (group)  ->  Advanced
 
Under Action, select “Copy to another location”. (If you select “in-place”, the sheet still contains all records, and Duplicate records are hidden. To view all records, use Unhide rows in the Format menu.)
 
In the List range box, select or enter range of records (ie. data range).
 
In the Copy to box, enter or select the first cell (viz. cell C2 in Table 1) where you want to copy the List of Unique Records.
 
Click and select, Unique records only.
 
Click OK. Unique records are copied to column C, cell C2 downwards.
 
Note:
If the first record of original data is duplicated, it appears twice in the new list. Just hide or delete the first row in this case.
 
In case of multiple columns, a list of unique records in respect of each row will be created, comparing records of all columns in that row simultaneously. (Refer Table 2, Table 3)
 
To “Copy to” a different worksheet (ie. to copy/extract a list of unique records to a different worksheet), you need to first go to that worksheet (viz. make it the active sheet) and then perform the abovementioned steps (Using Data Advanced Filter).
 
———————————————————————————————————————-
 
Copy all columns or specific columns
 
While doing a “Copy to another location”, you can copy/extract all columns or specify the columns you wish to extract.
 
Table 2
 
To copy all columns, in the Copy to box, enter or select the first cell (cell E2 in Table 2) or enter/select the headings where you want to copy the List of Unique Records. Unique records are copied to columns E, F & G.
  
Table 3
 
To copy specified columns, manually enter the ‘exact’ headings of these columns (headings ‘Name’ and ‘Nos.’ in Table 3) in the “Copy to” range and enter or select the same ($E$2:$F$2 in Table 3) in the “Copy to” box. The column order of the headings can be different from that in the “List range”. Unique records are copied to columns E & F.
 
——————————————————————————————————-
 
Criteria Range
 
It is optional to enter the “Criteria range” while doing Advanced Filter. This will enable copy of unique records, subject to the criteria specified.
 
Criteria range can be in one or multiple columns, and the ‘exact’ heading should be manually entered in a cell below which (in the same column) the criteria for that heading is to be entered.
 
————————————————————————————————————————-
 
Criteria is a set of rules and include:
 
Table 4
 
1. Operators like: > (greater than), < (less than), >= (greater than or equal to), <= (less than or equal to), <> (less or greater than). (Refer cell K3 in Table 4)
 
2. Specified Text: specifying text of say “Paul” will copy all records containing Paul, including Paul Spencer, (cell I3 in Table 4) whereas the format =”=Paul” will copy records which contain only Paul.
 
3. AND (operator): If the criteria is mentioned in one row (in multiple columns) in the Criteria Range, then list of unique records will be copied if all criteria is satisfied (viz. criteria is joined using the AND operator). Refer Table 4, use of AND operator viz. [(Name should be Paul) AND (Country should be USA) AND (Nos. should be >=4)].
 
 
Table 5
 
4. OR (operator): If the criteria is mentioned on different rows (in multiple columns) in the Criteria Range, then list of unique records will be copied if any of these criteria is satisfied (viz. criteria is joined using the OR operator). Refer Table 5, use of OR operator viz. [(Name should be Paul) OR (Country should be USA) OR (Nos. should be >=4)].
 
 
Table 6
5. AND / OR (operators) Combined: Both AND and OR operators can be combined using multiple rows (viz. criteria is joined using both the AND and OR operators). Refer Table 6, criteria is a combination viz.  [(Country should be USA AND the Nos. should be >4) OR (Name should be Eric AND Country should be France)]. The List of Unique Values is copied/extracted per this criteria.
 
 
Table 7
 
6. The (*) asterik wildcard, which indicates any number of characters (including nil) in that position. Cell I3 in Table 7 mentions Name should contain arl (with any number of characters before or after) which criteria is satisfied by ‘Tim Harlton’ and ‘Harley’ in column E.
 
7. The (?) question mark wildcard, which indicates any single character in that position. Cell J4 in Table 7 mentions Country should contain one character between ‘U’ and ‘A’, which criteria is satisfied by USA in column F.
 
Table 8
8. The (~) tilde wildcard, will search for characters that are used as wildcards. Use (~) tilde before the character to find question marks, asterisks and tildes which are used as text characters. Refer Table 8, cell I3 mentions name criteria ‘Harl*on’ which is satisfied by ‘Harlton’, ‘Harlington’ and ‘Harl*on’ in column E. Cell O3 mentions Name criteria ‘Harl~*on’ which is satisfied by ‘Harl*on’ in column K viz. using ~ recognizes wildcard character asterik (*) as text character asterik (*).
      
 
 

Leave a Reply

Your email address will not be published. Required fields are marked *

Scroll to top