Hello guys, if you are wondering how to remove duplicates from an Excel file, I mean duplicate values from an Excel column then you have come to the right place. You can use the "Remove Duplicate" option from the Excel toolbar to easily remove all the duplicates from a selected list of values. Alternatively, you can also use the COUNTIF function to remove duplicates from Excel columns. In this article, we'll see the example of both ways with step by step guide and screenshots. Microsoft Excel is one of the best tools to analyze data and I think every programmer should be familiar with this tool. There are a couple of genuine reasons why I am asking you to learn and master Excel. Since most of us work in Windows and Excel is almost always available, knowing how you can use it for your data analysis and reporting can really make a difference.
Managers, Directors, Clients, and People at a higher level just love reports and once your experience grows, your responsibility also grows and you need to create many reports or work on Excel sheets generated by others. It automatically becomes part of your life, especially if you are progressing in the project management space.
To be honest with you guys, even though I have more than 12 years of experience and I first u used Excel more than 15 years ago, my Excel skills are still poor for my experience. Barring some essential concepts like editing, filtering, sorting, removing duplicates, and some essential functions like SUM, VLOOKUP, etc, I don't know much.
One of the main reasons for that is I never used Microsoft Excel, like many others use it, and never realize its importance. I was just happy that I know Excel but I hardly knew any advanced concepts, but things have changed last year when I took a couple of courses to improve my Microsoft Skill like these best Microsoft Excel courses for Beginners, which contains best courses to learn Excel from Udemy, Pluralsight, and Coursera.
I have been doing some data analysis work lately and I found Excel really useful for analyzing data. For example, suppose you got a list of books sold from your log file and now you want to know which book has sold how many times?
Earlier I used to store that data in a temp table in SQL Server then run the GROUP BY query, a lot of work for a simple thing. But, as I said, things got improved in the last year, I come to know about many Excel functions, especially after joining another Advanced Excel Formulas and Functions course at Udemy, which can do a lot for you and one of them is COUNTIF.
As the name suggests, this function is a combination of COUNT and IF, which means it can count based upon a given condition.
As per Microsoft Excel documentation, COUNTIF(range, criterion) function counts the number of cells in a given range that matches the given condition. There is also a COUNTIFS() function which is similar to COUNTIF() but allows you to specify multiple conditions.
List of books
Effective Java
Head First Java
Cracking the Coding Interview
Algorithms
Head First Design Pattern
Head First Design Pattern
Head First Design Pattern
Head First Design Pattern
Head First Design Pattern
Algorithms
Algorithms
Algorithms
Algorithms
Algorithms
Algorithms
Algorithms
Head First Design Pattern
Head First Design Pattern
Effective Java
Head First Java
Head First Design Pattern
Head First Design Pattern
Head First Design Pattern
Head First Design Pattern
Cracking the Coding Interview
Cracking the Coding Interview
Cracking the Coding Interview
Cracking the Coding Interview
Cracking the Coding Interview
Algorithms
Head First Design Pattern
Head First Design Pattern
Cracking the Coding Interview
Cracking the Coding Interview
This will remove all the duplicates on that column and you will have a list of unique values as shown below:
This is the easiest way to remove duplicate values from a list using Microsoft Excel but this functionality is only available from Microsoft Excel 2013. This is also one of my favorite Excel tip which I learned from the Advanced Excel: Top Excel Tips and Formulas course from Udemy.
=COUNTIF(A1:A35,D1)
This function tells Microsoft Excel that search in the range from A1 to A35 for the value present in D. I have given D1 because my unique values are in column D and they start from the first row because there is no header.
If your Excel sheet has a header then your formula should say D2 because that's your first value.
Btw, You don't need to type the range just type the function and then choose the starting cell and drag to the last cell in the column you have pasted the list of values.
For populating criterion, just select the value in the list without duplicate. Once it is done for one cell you can just drag it to apply the same formula for all the cells as shown below.
Btw, if you were to do this on another sheet then you need to include the sheet name as well in your formula.
If you look at it closely, this is what GROUP BY does in SQL. You give a list of values and then it divides them into the group and tells you the count of each value.
That's all about how to group a list of values in Excel and found their count. It's a very useful tip while generating reports and I have found it using quite often. For example, you can use this trick to generate a sales report to find how many times each item is sold. You can then find the best seller or top 10 courses books etc.
To be honest with you guys, even though I have more than 12 years of experience and I first u used Excel more than 15 years ago, my Excel skills are still poor for my experience. Barring some essential concepts like editing, filtering, sorting, removing duplicates, and some essential functions like SUM, VLOOKUP, etc, I don't know much.
One of the main reasons for that is I never used Microsoft Excel, like many others use it, and never realize its importance. I was just happy that I know Excel but I hardly knew any advanced concepts, but things have changed last year when I took a couple of courses to improve my Microsoft Skill like these best Microsoft Excel courses for Beginners, which contains best courses to learn Excel from Udemy, Pluralsight, and Coursera.
I have been doing some data analysis work lately and I found Excel really useful for analyzing data. For example, suppose you got a list of books sold from your log file and now you want to know which book has sold how many times?
Earlier I used to store that data in a temp table in SQL Server then run the GROUP BY query, a lot of work for a simple thing. But, as I said, things got improved in the last year, I come to know about many Excel functions, especially after joining another Advanced Excel Formulas and Functions course at Udemy, which can do a lot for you and one of them is COUNTIF.
As the name suggests, this function is a combination of COUNT and IF, which means it can count based upon a given condition.
As per Microsoft Excel documentation, COUNTIF(range, criterion) function counts the number of cells in a given range that matches the given condition. There is also a COUNTIFS() function which is similar to COUNTIF() but allows you to specify multiple conditions.
2 Ways to remove Duplicate values from Excel - COUNTIF Example
In this article, I will share a tip that I have been using regularly. I have a list of values with me like. client name, books names, or item names and I need to find out the count of each item in the list. It's like GROUP BY in SQL and I'll show you how to do that in Excel using the COUNTIF function. That's why I have also put the title, how to do a group by in Excel.Step 1: Copy List of values into Excel
Let's paste the list of books we found in a column in our Excel sheet e.g. copy them into the first column as shown below.List of books
Effective Java
Head First Java
Cracking the Coding Interview
Algorithms
Head First Design Pattern
Head First Design Pattern
Head First Design Pattern
Head First Design Pattern
Head First Design Pattern
Algorithms
Algorithms
Algorithms
Algorithms
Algorithms
Algorithms
Algorithms
Head First Design Pattern
Head First Design Pattern
Effective Java
Head First Java
Head First Design Pattern
Head First Design Pattern
Head First Design Pattern
Head First Design Pattern
Cracking the Coding Interview
Cracking the Coding Interview
Cracking the Coding Interview
Cracking the Coding Interview
Cracking the Coding Interview
Algorithms
Head First Design Pattern
Head First Design Pattern
Cracking the Coding Interview
Cracking the Coding Interview
Step 2: Remove Duplicates
In order to display the book and its count, we first need to remove duplicates. For that just copy the list of books into another column and click on remove duplicates as shown in the following screenshot.This will remove all the duplicates on that column and you will have a list of unique values as shown below:
This is the easiest way to remove duplicate values from a list using Microsoft Excel but this functionality is only available from Microsoft Excel 2013. This is also one of my favorite Excel tip which I learned from the Advanced Excel: Top Excel Tips and Formulas course from Udemy.
Step 3: Create Formula using the COUNTIF function
Once you did that, just add a formula in the next column to show their count using the COUNTIF function as shown below:=COUNTIF(A1:A35,D1)
This function tells Microsoft Excel that search in the range from A1 to A35 for the value present in D. I have given D1 because my unique values are in column D and they start from the first row because there is no header.
If your Excel sheet has a header then your formula should say D2 because that's your first value.
Btw, You don't need to type the range just type the function and then choose the starting cell and drag to the last cell in the column you have pasted the list of values.
For populating criterion, just select the value in the list without duplicate. Once it is done for one cell you can just drag it to apply the same formula for all the cells as shown below.
Btw, if you were to do this on another sheet then you need to include the sheet name as well in your formula.
If you look at it closely, this is what GROUP BY does in SQL. You give a list of values and then it divides them into the group and tells you the count of each value.
That's all about how to group a list of values in Excel and found their count. It's a very useful tip while generating reports and I have found it using quite often. For example, you can use this trick to generate a sales report to find how many times each item is sold. You can then find the best seller or top 10 courses books etc.
Btw, I still think, I don't know much about Excel but after realizing its importance in my career, I am also taking an Excel fundamentals course to improve my Excel skills. you can join too.
Other Useful Tips and Resources for Programmers Thanks for reading this article so far, if you like this article then please share it with your friends and colleagues. If you have any feedback or question then please drop a note.
Other Useful Tips and Resources for Programmers Thanks for reading this article so far, if you like this article then please share it with your friends and colleagues. If you have any feedback or question then please drop a note.
P. S. - If you are new to Microsoft Excel and want to learn Excel in-depth then I also suggest you to check out these free Microsoft Excel courses to start with. The list contains free Udemy and Coursera courses to learn Microsoft Excel from scratch.
No comments:
Post a Comment