Removing Duplicates in Excel

So most people accustomed to Excel will probably know this, but there is a much easier way for removing duplicates than sifting through the info line by line, by monotonous line. With a few clicks of your mouse you should be able to remove any and all duplicates, once again easy-peasy, keeping in mind that each version of Excel is slightly different (I’m using 2010 at home and 2013 at work – bar the cosmetic touches and slight variation on VB coding language there isn’t much difference anyway). Let’s get started.

In the previous post I made mention of Sam’s travel agency, so let’s stick to what we know. Sam has kept a database of ALL her transactions for the past year, and would like to check who all her travellers are (taking into account multiple travellers travel multiple times. Let’s help her out.

All other information in this small example is inconsequential, as the only info we want extracted is from column B.

See that Data header, we select that and select Advanced. You’ll notice the very ominous looking explanation of specify complex criteria blah blah blah set of a query. They really could’ve just said Let’s remove all the duplicates in a range.

First things first,

·          - Select Copy to another location

·          - Select Unique records only

·          - Select your list which you’d like to remove the duplicates, in this case Column B cell 1 to 14

·          - And lastly select where you’d like to copy your shortened and devoid of duplicates list (this can be anywhere – well not in your        underwear drawer, keep it simple and in Excel).

And voila! Your duplicates have been removed. (This example has been significantly oversimplified but you should get the idea. Having thirteen names and thirteen thousand names will put this into perspective)

Hope this helps those that aren’t working on Excel on a day-to-day basis

Custom COUNTIF Formula: Detecting Duplicate entries

Ok, for the first entry, excel virginity hopefully broken by the few who would read this blog entry, we start off with an easy yet tricky formula which, if done correctly, will really, REALLY save you time and monotonous work. (Please note this is a custom formula and cannot be done with the ‘automatic formulas’ on Excel)

Let’s take Sam’s Travel Agency as an example. Sam has bookings for numerous clients, all private. We have entries of up to ten thousand bookings in the last few months. The clients fly from Point A to Point B, even Point C and Point D all on different flight numbers, but she needs to bill these clients per trip, not flights. So here’s what we do.

First things first, distinguish the common element, in this case the trips in column C, which you’d like to bill (theoretically a service fee in this case) as a whole.

We enter the formula =COUNTIF($C$2:C2,C2)=1

This will give you the end result of True vs False for the amount of duplicate trip numbers. One True, and the rest false for all the other duplicates. Easy peasy, for three entries that is. For those that haven’t lost interest, let’s use an IF statement to calculate the service fees on multiple trips.

By simply entering an IF statement, we’ve saved time by stating that for every TRUE (which if anyone’s following is our singular trip and is to be charged one service fee) is equal to a $50 service fee and can then be dragged or autofilled in the column to give us an accurate indication of how much service fees Sam has to charge her clients. Done and dusted. Hopefully…