In the following dataset, we have the Student Name, Marks, and Grade columns. The names of some students have been repeated along with their marks and grades. We’ll remove those duplicate rows.
Method 1 – Use the UNIQUE Function to Eliminate Duplicates
The UNIQUE function is only available in Excel 365 onward.
Case 1 – Completely Removing the Values that Appear More than Once
- Use the following formula in cell G5.
=UNIQUE(B5:D15,FALSE, TRUE)
Formula Breakdown
- UNIQUE(B5:D15,FALSE, TRUE) → the UNIQUE function finds a number of unique data in an array.
- B5:D15 → indicates an array.
- FALSE → indicates by_col.
- TRUE → indicates that the duplicates will be removed completely.
- Hit Enter.
- The unique values will be listed in cells G5:I11.
Notes:
- Three names of the students had duplicates: David Moyes, Angela Hopkins, and Brad Milford.
- Among them, David Moyes and Brad Milford have been removed completely.
- Angela Hopkins has not been removed because the marks and grades of two Angela Hopkins are not the same. That means they are two different students.
Case 2 – Keeping One Copy of the Values that Appear More than Once
- Use the following formula in cell G5.
=UNIQUE(B5:D15,FALSE,FALSE)
- Hit Enter.
- You can see the result in cells G5:I13.
Read More: How to Remove Duplicates Based on Criteria in Excel
Method 2 – Combine the CONCATENATE, FILTER, and COUNTIF Functions to Remove Clones
Steps:
- Make a new column and insert the following formula in cell E5.
=CONCATENATE(B5:B15,C5:C15,D5:D15)The CONCATENATE function sums up three columns into one.
- Press Enter.
- Go to another new column and insert the following formula in cell G5.
=FILTER(B5:B15,COUNTIF($E$5:$E$15,$E$5:$E$15)=1) 
Formula Breakdown
- COUNTIF($E$5:$E$15,$E$5:$E$15)=1 → the COUNTIF function counts cell numbers that meet the criteria.
- FILTER(B5:B15,COUNTIF($E$5:$E$15,$E$5:$E$15)=1) → the FILTER function filters a range of data based on defined criteria.
- Hit Enter.
- You can see the unique values in cells G5:G11.
- Drag the Fill Handle right up to the total number of your columns (3 in this example).
- You will get the whole data set without the duplicate values.
Note:
- You can remove all the values that appear more than once.
- You can’t keep one copy of the duplicate values as mentioned in the earlier method.
Method 3 – Merge IFERROR, INDEX, SMALL, CONCATENATE, IF, and COUNTIF Functions
Steps:
- Make a new column and insert this formula in cell E5.
=CONCATENATE(B5:B15,C5:C15,D5:D15) 
Formula Breakdown
- The CONCATENATE function merges the three columns into one single column.
- Since this is an Array Formula, press Ctrl + Shift + Enter unless you are in Office 365.
- You can see the complete column with merged values.
- Use the following formula in cell G5.
=IFERROR(INDEX(B5:D15,SMALL(IF(COUNTIF(E5:E15,E5:E15)=1,ROW(E5:E15)-ROWS(E2:E4),""),ROW(E5:E15)-ROWS(E2:E4)),{1,2,3}),"")
Formula Breakdown
- The ROWS function finds out the number of rows in an array.
- The IF function does a logical comparison between a given value and the value we expect.
- The COUNTIF function counts a number of cells based on criteria.
- The INDEX function finds the data from a data range.
- The SMALL function gives the output of kth lowest value in an array.
- The IFERROR function returns a blank cell when the formula contains an error.
- Hit Enter.
- The cells G5:I11 do not contain any duplicate values.
Note:
- You can also remove all the values that appear more than once
- However, you can’t keep one copy of the duplicate values as mentioned in the earlier method.
Method 4 – Utilize the VLOOKUP Function for Removing Duplicates Automatically
We have modified the dataset to contain the Book List-1 and Book List-2 columns. We will use VLOOKUP to get rid of duplicates.
Steps:
- Create another column D after Book List-2, named Values.
- Select cell D5 and use the following formula.
=VLOOKUP(C5,$B$5:$B$14,1,FALSE) 
Formula Breakdown
- VLOOKUP(C5,$B$5:$B$14,1,FALSE) → the VLOOKUP function searches for a value in an array.
- Lookup_value is C5.
- Table_array is $B$5:$B$14.
- Col_index_num is 1.
- [range_lookup] is FALSE as we want the exact match
- Output: The Golden Bowl
- Explanation: The Golden Bowl is the duplicate value.
- Hit Enter.
- Drag down the formula with the Fill Handle tool.
- If there are any unique values in those two columns, the function will return the #N/A error.
- Select the headings of the dataset and go to the Data tab.
- From the Sort & Filter group, select Filter.
- You’ll get the Filter icon in every column of the dataset.
- Click on the drop-down icon in the Values column, and from the sorting option, check #N/A and click OK.
- This hides the rows that contain duplicates.
Read More: How to Remove Duplicates Using VLOOKUP in Excel
Method 5 – Integrate VLOOKUP, IF, and ISERROR Functions
Steps:
- We will use the same dataset from the previous method.
- Use the following formula in cell D5.
=IF(ISERROR(VLOOKUP(C5,$B$5:$B$14,1,FALSE)),"Unique","Duplicate")
Formula Breakdown
- VLOOKUP(C5,$B$5:$B$14,1,FALSE) → this formula will find out the exact match of cell C5 value in the range of cells $B$5:$B$14. Here, Lookup_value is C5, Table_array is $B$5:$B$14. Col_index_num is 1 and [range_lookup] is (FALSE) as we want the exact match.
- IF(ISERROR(VLOOKUP(C5,$B$5:$B$14,1,FALSE)),”Unique”,”Duplicate”) → If the value is true, the formula will return “Unique”. If the value is false, the formula will return “Duplicate’’.
- Output: D5
- Explanation: D5 returns Duplicate since the formula detects a duplicate value.
- Press Enter.
- Drag down the formula with the Fill Handle tool.
- Select the headings of the dataset and go to the Data tab.
- From the Sort & Filter group, select Filter.
- Click on the drop-down icon of the Values columns.
- Unmark Duplicates since we want to remove them.
- Click OK.
- We only have unique values in the dataset.
Method 6 – Use TEXTJOIN and COUNTIF Functions
We have added the Combined and Count columns in our dataset. We will use the TEXTJOIN function to find the combined values in the Combined column. After that, we will use the COUNTIF function to find the duplicate number in the Count column.
Steps:
- Use the following formula in cell D5.
=TEXTJOIN("", FALSE,B5:C5)
Formula Breakdown
- TEXTJOIN(“”, FALSE,B5:C5) → the TEXTJOIN function adds text from multiple ranges.
- Output: Frank Orwell99
- Explanation: Frank Orwell99 is the combined text of cells B5 and C5.
- Hit Enter.
- Drag down the formula with the Fill Handle tool.
- You can see the complete Combined column.
- Use the following formula in cell E5.
=COUNTIF($D$5:$D$15,D5)The COUNTIF function counts the number of cells based on criteria.
- Press Enter.
- Drag down the formula with the Fill Handle tool.
- You can see the complete Count column. In the Count column, the value is 1 for unique values and 2 for duplicate values.
- Select the headings of the dataset and go to the Data tab.
- From the Sort & Filter group, select Filter.
- Click on the drop-down icon of the Count column.
- Uncheck 2 and click OK.
- You can see that the dataset has no duplicate values.
How to Remove Duplicates Using the Remove Duplicates Tool
Steps:
- Select the whole data set.
- Go to the Data tab.
- From the Data Tools group, select Remove Duplicates.
- A Remove Duplicates dialog box will appear.
- Select all the columns and click OK.
- You will get a confirmation box. Click OK.
- We have removed the duplicate Student Name.
Note: In this method, one copy of the duplicate row will remain. You can’t completely remove the duplicate rows.
Read More: How to Find & Remove Duplicate Rows in Excel
Practice Section
You can download the above Excel file and practice the explained methods.
Download the Practice Workbook
Related Articles
- How to Remove Duplicate Rows Based on One Column in Excel
- How to Remove Duplicate Rows in Excel Based on Two Columns
- How to Remove Duplicates from Columns in Excel
- Hide Duplicate Rows Based on One Column in Excel
- How to Remove Duplicate Rows in Excel Table
<< Go Back to Duplicates in Excel | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
