To be trustworthy with you guys, although I’ve greater than 12 years of expertise and I first u used Excel greater than 15 years in the past, my Excel expertise are nonetheless poor for my expertise. Barring some important ideas like enhancing, filtering, sorting, eradicating duplicates, and a few important capabilities like SUM, VLOOKUP, and many others, I do not know a lot.
One of many essential causes for that’s I by no means used Microsoft Excel, like many others use it, and by no means understand its significance. I used to be simply comfortable that I do know Excel however I hardly knew any superior ideas, however issues have modified final 12 months once I took a few programs to enhance my Microsoft Talent like these greatest Microsoft Excel programs for Newcomers, which comprises greatest programs to study Excel from Udemy, Pluralsight, and Coursera.
I’ve been doing a little information evaluation work these days and I discovered Excel actually helpful for analyzing information. For instance, suppose you bought an inventory of books bought out of your log file and now you wish to know which ebook has bought what number of instances?
Earlier I used to retailer that information in a temp desk in SQL Server then run the GROUP BY question, a whole lot of work for a easy factor. However, as I mentioned, issues obtained improved within the final 12 months, I come to find out about many Excel capabilities, particularly after becoming a member of one other Superior Excel Formulation and Capabilities course at Udemy, which may do quite a bit for you and considered one of them is COUNTIF.
Because the title suggests, this operate is a mixture of COUNT and IF, which implies it might depend primarily based upon a given situation.
As per Microsoft Excel documentation, COUNTIF(vary, criterion) operate counts the variety of cells in a given vary that matches the given situation. There’s additionally a COUNTIFS() operate which has similarities to COUNTIF() however lets you specify a number of circumstances.
2 Methods to take away Duplicate values from Excel – COUNTIF Instance
On this article, I’ll share a tip that I’ve been utilizing often. I’ve an inventory of values with me like. shopper title, books names, or merchandise names and I want to search out out the depend of every merchandise within the checklist. It is like GROUP BY in SQL and I will present you the way to do this in Excel utilizing the COUNTIF operate. That is why I’ve additionally put the title, do a bunch by in Excel.
Step 1: Copy Listing of values into Excel
Let’s paste the checklist of books we present in a column in our Excel sheet e.g. copy them into the primary column as proven beneath.
Listing of books
Efficient Java
Head First Java
Cracking the Coding Interview
Algorithms
Head First Design Sample
Head First Design Sample
Head First Design Sample
Head First Design Sample
Head First Design Sample
Algorithms
Algorithms
Algorithms
Algorithms
Algorithms
Algorithms
Algorithms
Head First Design Sample
Head First Design Sample
Efficient Java
Head First Java
Head First Design Sample
Head First Design Sample
Head First Design Sample
Head First Design Sample
Cracking the Coding Interview
Cracking the Coding Interview
Cracking the Coding Interview
Cracking the Coding Interview
Cracking the Coding Interview
Algorithms
Head First Design Sample
Head First Design Sample
Cracking the Coding Interview
Cracking the Coding Interview
Step 2: Take away Duplicates
So as to show the ebook and its depend, we first must take away duplicates. For that simply copy the checklist of books into one other column and click on on take away duplicates as proven within the following screenshot.
It will take away all of the duplicates on that column and you should have an inventory of distinctive values as proven beneath:
That is the best approach to take away duplicate values from an inventory utilizing Microsoft Excel however this performance is just accessible from Microsoft Excel 2013. That is additionally considered one of my favourite Excel tip which I realized from the Superior Excel: Prime Excel Suggestions and Formulation course from Udemy.
Step 3: Create Components utilizing the COUNTIF operate
As soon as you probably did that, simply add a method within the subsequent column to point out their depend utilizing the COUNTIF operate as proven beneath:
=COUNTIF(A1:A35,D1)
This operate tells Microsoft Excel that search within the vary from A1 to A35 for the worth current in D. I’ve given D1 as a result of my distinctive values are in column D they usually begin from the primary row as a result of there is no such thing as a header.
In case your Excel sheet has a header then your method ought to say D2 as a result of that is your first worth.
Btw, You needn’t sort the vary simply sort the operate after which select the beginning cell and drag to the final cell within the column you will have pasted the checklist of values.
For populating criterion, simply choose the worth within the checklist with out duplicate. As soon as it’s achieved for one cell you possibly can simply drag it to use the identical method for all of the cells as proven beneath.
Btw, should you have been to do that on one other sheet then you have to embody the sheet title as effectively in your method.
When you take a look at it carefully, that is what GROUP BY does in SQL. You give an inventory of values after which it divides them into the group and tells you the depend of every worth.
That is all about group an inventory of values in Excel and located their depend. It is a very helpful tip whereas producing studies and I’ve discovered it utilizing very often. For instance, you should use this trick to generate a gross sales report to search out what number of instances every merchandise is bought. You may then discover the perfect vendor or high 10 programs books and many others.
