Suppose you have a list as given below and now task before you is to generate a list of unique / distinct list from column A. We will also be looking into generating Unique values from Columns A, B and C together which can be generalized to any number of columns.
Note – This article is for the list which has no blanks in the range select / list. The case of having blanks in data range will be investigated into another article.
We will revert to tackle this problem in 6 ways
A. Manual Way
B. Hybrid Way
C. Formula Way
D. MS Query Way
E. Power Pivot Way
F. VBA Way
The Excel file related to this article can be downloaded from Unique List_No Blanks
A. Manual Way
A.1 Advanced Filter Technique
A.1.1 Only Column A is Criterion (Uniqueness is decided on the basis of column A)
1. Select the column A only and go to Data Tab>Advanced
2. Decide whether you want to filter the list in place or you want to copy to another location.
2.1 In case, you filter the list in place, duplicate rows will be hidden.
2.2. Recommended method is to copy to another location. If you choose this, Copy to: box gets enabled.
Limitation – You can copy only that column which you selected. You will have to leave out all other columns. And that is the advantage of step 2.1 as here all columns are preserved and then you can manually copy and paste the unique records by selecting the results.
3. In List Range: box, put the range for column A only. By default, it takes both column A and adjacent columns data. Hence, you may choose $A$1:$A20 in case of my example, or you can give even $A:$A also.
4. Check Unique Records Only box and press OK.
A.1.2 Both Columns A &B are Criteria (Uniqueness is decided on the basis of column A & B)
Follow the steps given in section A1.1.1. The only change is that your List Range: box now includes both columns.
A.1.3 Columns A, B & C are Criteria (Uniqueness is decided on the basis of column A, B & C)
Follow the steps given in section A1.1.1. The only change is that your List Range: box now includes all 3 columns.
A.2 Remove Duplicates Technique
Discretion Advised – This deletes your non unique values. Hence, discretion advised. Also, in case of duplicates, it keeps the first value and deletes remaining values.
A.2.1 Only Column A is Criterion
1. Select all columns A, B & C. (You have to select all columns otherwise it will remove duplicates only from the column selected. Though it offers you the opportunity whether you want to continue with current selection or expand the selection in step2.)
2. Data Tab> Remove Duplicates > Uncheck column B & C and press OK.
A.2.2 Both Columns A &B are Criteria
In Step 2 of A.2.1, un-check column C
A.2.3 Columns A, B & C are Criteria
In Step 2 of A.2.1, don't un-check any column
A.3 Pivot Table Way
Another quick and handy way to generate the list is to use Pivot Table. I consider this method to be superior to any other manual method as every other manual method, you will need to do every time data is changed. But, in case of Pivot Table, you just need to refresh it if the Pivot Table is setup for entire column not only for data range.
Limitation – Only that column will be displayed which is criteria. Hence, if column A is criterion, you can't display other columns.
A.3.1 Only column A is criteria
1. Select all columns A, B & C.
2. Insert tab > Pivot Table
3. Drag column A header (in this case Name) to Rows Area.
4. Remove blanks in Pivot Table by applying filter on column 1 header i.e Name here.
5. Optional – Right Click on Pivot Table > Pivot Table Options > In Totals & Filters, Uncheck Show grand total for columns
A.3.1 When more than one column is criterion
1. Right Click on Pivot Table > Pivot Table Options > In Display tab, check Classic PivotTable Layout
2. Drag other fields to Rows Area
3. Mark subtotals to None in all fields in the Rows Area
B. Hybrid Way
B.1 Conditional Formatting Way
B1.1 When Column A is the criterion
1. Select the require range i.e. B2 to B20
2. Home tab > Conditional Formatting > New Rule > Use a formula to determine which cells to format and put following formula
3. =COUNTIF($A$2:A2,A2)=1
4. Choose required fill colour
5. Filter on this colour and copy and paste the unique list in some other place.
B1.2 When Column A,B & C are the criteria
If the table is in columns H to J, the conditional formatting formula would be –
=SUMPRODUCT(–($H$2:H2&$I$2:I2&$J$2:J2=H2&I2&J2))=1
B.2 New Column Introduction Way
B.2.1 When column A is the criterion
1. In D2 put following formula and drag down
=COUNTIF($A$2:A2,A2)=1
2. Apply filter on Column D = "YES"
3. Copy and Paste filtered Unique list somewhere else
B.2.1 When columns A, B & C are the criteria
1. If the table is in columns H to J, put following formula in D2 and drag down
=SUMPRODUCT(–($H$2:H2&$I$2:I2&$J$2:J2=H2&I2&J2))=1
2. Apply filter on Column D = "YES"
3. Copy and Paste filtered Unique list somewhere else
C. Formula Way
For below formula to work, you will need to give exact range. Hence, formula way can't be applied for the range which is extending beyond your data range otherwise it will bring blanks after your data range. Blanks is something which I have promised to handle in a different article.
C.1 When only one column is criterion, here only column A
Enter following formula in E2 and drag down till E20
=IFERROR(INDEX($A$2:$A$20,MATCH(0,INDEX(COUNTIF($E$1:$E1,$A$2:$A$20),0,0),)),"")
Note – This formula is non-array version of popular array formula which we find on internet on almost all sites – =INDEX($A$2:$A$20,MATCH(0,COUNTIF($E1:$E$1,$A$2:$A$20),0))
My value addition has been to convert this array formula to non-array version (and enclose in IFERROR block).
To extract columns B and C, put following formulas in F2 and G2 and drag down.
=IF(E2="","",VLOOKUP(E2,$A$2:$C$20,2,0))
OR
=IFERROR(INDEX($B$2:$B$20,MATCH(0,INDEX(COUNTIF($E$1:$E1,$A$2:$A$20),0,0),)),"")
=IF(E2="","",VLOOKUP(E2,$A$2:$C$20,3,0))
OR
=IFERROR(INDEX($C$2:$C$20,MATCH(0,INDEX(COUNTIF($E$1:$E1,$A$2:$A$20),0,0),)),"")
C.2 When more than one column is criterion, here columns A & B
Here, you will have to use two helper columns.
First helper column will concatenate first two columns (which are criteria. If three columns are criteria, then concatenation will be done on first 3 columns and so on)
Next helper column will generate a unique list of first helper column. Hence, below is the formula as in C1.
=IFERROR(INDEX($J$2:$J$20,MATCH(0,INDEX(COUNTIF($K$1:$K1,$J$2:$J$20),),0)),"")
Columns M and N have following formulas –
=IF(K2="","",INDEX($G$2:$G$20,MATCH(K2,$J$2:$J$20,0)))
=IF(K2="","",INDEX($H$2:$H$20,MATCH(K2,$J$2:$J$20,0)))
C.3 When more than one column is criterion, here columns A, B & C
This is just an extension of C2. First helper column has formula –
=P2&Q2&R2
Second helper column generates a unique list on first helper column –
=IFERROR(INDEX($S$2:$S$20,MATCH(0,INDEX(COUNTIF($T$1:$T1,$S$2:$S$20),),0)),"")
Columns V, W and X have following formulas –
=IF(T2="","",INDEX($P$2:$P$20,MATCH(T2,$S$2:$S$20,0)))
=IF(T2="","",INDEX($Q$2:$Q$20,MATCH(T2,$S$2:$S$20,0)))
=IF(T2="","",INDEX($R$2:$R$20,MATCH(T2,$S$2:$S$20,0)))
D. MS Query Way
MS Query is an important tool when the operation is repetitive and needs to be executed by end users. It eliminates the need for other approaches. What an user needs to do is to just run the query and he gets the output. Also, this works on closed file thus eliminating the need for opening the file, a first rate advantage.
The first and foremost requirement for MS Query is that the data to be considered should be a named range. In the Excel for this Article, I have prepared a table in MS Query Way tab. I have selected range A1:C100 and named it. (Caution – Avoid selecting full column range otherwise query running will take few minutes. Select that range which is maximum data anticipated)
1. Data tab > From Other Sources > From Microsoft Query
2. Now you get Choose Data Source > Excel Files* > OK
3. In the next screen, select the Excel File where data is hosted and named range has been created.
4. Select the required Excel file and the named range. Add the column on which you want uniqueness (Now, it introduces a limitation like pivot table and Advance Filter > Copy to another location i.e it will display only that column(s) on which uniqueness is wanted.)
5. Keep pressing Next and on last screen, select View Data or Edit Query in Microsoft Query > Finish
6. View > Query Properties > Tick Unique Value Only
7. Now, this query can be saved by File > Save As
— intentional line gap—
8. Now, whenever user wants to run this Query, he just needs to Data tab > From Other Sources > From Microsoft Query
9. In the next screen which is Choose Data Source > Queries tab > Select the Saved query and run.
D. Power Pivot Way
I don't have Power Pivot on my Laptop as this has to be purchased for MS Office 2013 whereas it was free for MS Office 2010. Hence, I am not an expert here. But since this is a method which should be included as part of this article, hence I am including. The maximum I am able to do is to point towards the Microsoft link which talks about this by using DAX function.
http://office.microsoft.com/en-in/excel-help/distinct-function-dax-HA102838209.aspx
E. VBA Way
I talked about MS Query which could be run by users to generate the required output (advantage was that Queries can be run on closed files also). Now, to do the same thing, users can also run Macros. These macros can be stored in Personal Workbook so that they are available to all the workbooks. User can open the workbooks and simply run macros. They can also assign shortcuts to macros which is another strength. There are N number of ways to achieve anything in VBA, below ones are just one of N ways –
E.1. Macro code for First Column is the criterion > Advanced Filter > Filter in Place
Sub One_Column_FilterInPlace_Name_Unique()
Columns("A:A").AdvancedFilter Action:=xlFilterInPlace, Unique:=True
End Sub
E.2. Macro code for First Column is the criterion > Advanced Filter > Copy to Another Location –
Copy is being done at G1
Sub One_Column_CopyToAnotherLocation_Name_Unqiue()
Columns("A:A").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("G1" _
), Unique:=True
End Sub
E.3. Macro code for Three Columns is the criteria > Advanced Filter > Filter in Place
Sub Three_Columns_FilterInPlace_Unique()
Columns("A:C").AdvancedFilter Action:=xlFilterInPlace, Unique:=True
End Sub
E.4. Macro code for Three Columns is the criteria > Advanced Filter > Copy to Another Location –
Copy is being done at G1
Sub Three_Columns_CopyToAnotherLocation_Unique()
Columns("A:C").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("G1" _
), Unique:=True
End Sub
Note – Various options of above Macros / VBA are possible. It all depends upon individual needs and requirements.