How To Find Duplicate Value In Excel Using Formula
When y'all are working on a large number of data so y'all will run across a few duplicate entries.
Sometimes these indistinguishable values are very useful only many times y'all desire to remove the duplicate data. If you have 1000 information then it becomes quite hard to find and remove the duplicate data.
Hither nosotros have provided the step-by-step process on how to find duplicates in excel.
Take a await and follow the procedure.
How to Find Duplicates in Excel
There are many means to find indistinguishable items and values in excel. You might be thinking equally to why should I apply any formula or method to observe duplicate values as information technology is easy.
Merely information technology's not about few data, you can apply a formula or method when you lot accept lots of information.
The method or formula to notice and remove the duplicate items make the procedure easier and saves your time. Here you tin can bank check three different processes.
1. Find Duplicates in Excel using Conditional Formatting
To observe duplicate values in Excel, you can employ provisional formatting excel formula, Vlookup, and Countif formula. Afterward finding out the duplicate values, you can remove them if y'all want by using dissimilar methods that are described below.
Remove indistinguishable values
- Select all the duplicate cells or highlighted cells
- Delete the values by pressing the Delete button.
- Afterward deleting the values, go to the conditional formatting.
- Choose Clear rules.
- And and then cull clear rules from an entire canvass.
Another style to remove the duplicate value
This method will delete all the duplicate data permanently. If you want the duplicate data again, then copy the information on another worksheet.
- Select the cells on which yous want to find and remove the duplicate values.
- Become to the ribbon and detect the data selection.
- In the data tab, you lot will find the remove duplicates pick.
- Now check or uncheck the column on which you lot want to utilize this constraint.
- Then click OK.
ii. Find Duplicates in One Column using COUNTIF
Some other way to detect duplicates is via the COUNTIF function. If you want to employ the COUNTIF function so check hither how to detect duplicates in Excel using the Excel COUNT function.
- If yous take the list of items from where you want to notice duplicates, so you lot need to apply the COUNTIF formula to information technology.
- The syntax for COUNTIF: "=COUNTIF(B:B, B2)>i".
- In column A, you have the Heir-apparent'southward name and in cavalcade B, you have the fruits name that he or she likes. At present you want to find the duplicate items.
- Put this COUNTIF formula in column C.
- For all indistinguishable fields, it shows TRUE whereas, for non-duplicate fields, it shows Imitation.
- Now by post-obit the above step-by-stride process, you can delete all the indistinguishable items that you practice not need.
Find Duplicates in Two Columns in Excel
Above nosotros have seen how to find duplicate values in one cavalcade, at present we will see hither how to find duplicates in two columns in excel.
In this instance, we have taken a tabular array where the candidate proper name is in column A and Fruits is in column B. Now we want to notice duplicate values having the aforementioned name and fruits.
The formula to observe duplicate values in two columns is
=If (Countifs($A$2:$A$viii, $A2, $B$2:$B$eight, $B2)>one, "Indistinguishable Values"," ")
How to Count Duplicates in Excel
If you lot want to know the total number of duplicate values then you lot demand to use the Count part. For counting indistinguishable values you need to apply the countif formula.
=Countif($A$2:$A$eight, $A2)
iii. Filter Duplicates in Excel
Some other method to find duplicates in excel is to filter data to get duplicate values.
Remove Duplicates Online
To remove duplicates, you need to follow the below-mentioned process
- Select the tabular array by dragging the mouse.
- Correct-click on the table.
- Delete the row by choosing the Delete Row choice.
Highlight Duplicates in Excel
In club to highlight the duplicate values, you can either utilize a conditional formula to notice duplicates in excel or follows the below-mentioned procedure.
- Select the indistinguishable values.
- Go to the Fill color option and select the color.
- This volition highlight the duplicate values in excel.
Apart from these, there are other processes bachelor by which yous tin can find the duplicate items bachelor in your data set. After finding out the duplicate values, you can remove them easily. Stay connected with our website to know other Excel features.
Related Queries
Here we have listed a few queries that are asked by Excel learners. If you are likewise facing this kind of problem and then you tin check it here.
Q1. How to delete indistinguishable e-mail addresses if yous have 10,000 emails in your data set up?
Ans. If you have all e-mail addresses in a unmarried column then select the column and become to the "Information" ribbon. You will find the 'Remove Duplicates' tab in data tools.
Click on Remove Duplicates and you lot will find another dialog box where you need to brand a selection. Press Ok and you receive the unique email addresses.
Q2. How to detect the repeated teacher'south name in Column A? The data is
- B1= Maths/English Jasmine, Eon
- B2= Physics Johana
- B3= History Poole
- B4= English/Physics Sam, Jasmine
- B5= Chemical science Romeo
Ans. To solve this problem, create three divide columns. In the first cavalcade, mention the class name. In the second column, put the teacher's proper name, and in the third mention another teacher'southward name. Now in the fourth cavalcade, put the formula =COUNTIF(B:B,B1)>ane
Using this formula, you lot will detect "TRUE" if the value is indistinguishable.
Q3. How to sort data before applying a filter?
Ans. To sort data you demand to cull the Sort tab in excel. Sorting a data set is quite an easy procedure. Follow the procedure mentioned below and sort your data completely.
- Select the data fix.
- Become to the Information ribbon.
- In the Sort & Filter department, you volition detect the Sort icon. Click on the icon and sort your data.
Q4. How to compare two columns in excel?
This question was mainly asked by the interviewer at the time of the interview. There are many ways to compare the 2 columns:
- =A2=B2 This is the simple formula used to compare two columns.
- Conditional Formatting: In this, you need to follow the higher up process, you tin check the conditional formatting excel link to get detailed information.
- Vlookup: You can also use the Vlookup formula to compare two columns using Vlookup for research, ISNA for performance, and If to customize the consequence.
- Excel If Statement: This is a quite useful function in excel and information technology tin be used in many ways. To get the detailed data Click Here.
Q5. How to filter information in excel?
Filtering in Excel is an easy process. In the sort & filter section, you will discover the Filter icon. Click on the icon and filter your data.
In this tutorial, we have covered all the excel topics that are best for beginners and experts. Nosotros always effort to solve all the problems that people face while working on excel. If you have any other query or want to ask something and so you lot can mention in the comment box below. I of our team members will be back with the answer.
Yous may also similar the following Excel tutorials:
- How to Remove Formulas in Excel (and keep the data)
- How to Fill Bare Cells with Value to a higher place in Excel
- How to Generate Random Numbers in Excel (Without Duplicates)
- How to Find Merged Cells in Excel
- Highlight Cell If Value Exists in Another Column in Excel
- Using Conditional Formatting with OR Criteria in Excel
- How to Remove Duplicate Rows based on i Column in Excel?
Source: https://spreadsheetplanet.com/how-to-find-duplicates-in-excel/
Posted by: parkreakes2000.blogspot.com
0 Response to "How To Find Duplicate Value In Excel Using Formula"
Post a Comment