Unfortunately the database I use is not overly advanced and when I
export data form it and include contact name it duplicates entries
(long story!)
Anyway, I would like to create a formula that will look at the data I
have exported to excel and check for duplicate entries and then remove
them. At the moment I am having to check them manually and remove
them and its taking me ages!
Help would be appreciated
One nice and easy (quick and dirty) way is as follows. (Don't worry
about the length of the instructions below; it takes longer to read
them than to do this once you get used to it.)
Make sure that your data has column headings.
Let's say that your contact names are in column A. In the first blank
column, enter the following formula in row 2:
=COUNTIF(A$1:A1,A2)
Copy that formula down as far as your list goes. Note that the formula
will automatically change as you copy it down, so in row 3 it will be:
=COUNTIF(A$1:A2,A3),
then in row 4
=COUNTIF(A$1:A3,A4) and so on.
It will calculate the number of entries which match the current
contact in the rows above the current one. If there are no previous
matches, it will return zero. If there ARE previous matches, it will
return the number of them.
Now go to Data -> Filter -> AutoFilter. In the CountIf column, set the
filter value to 0. In other words, you're telling it to find the first
unique records. All the rest will be hidden.
Want to extract them? Select the table by pressing [Ctrl] + [*], then
Edit -> Goto -> Special -> Visible Cells Only. Copy the selection,
then go and paste the filtered list into another sheet.
Note that Data -> Filter -> Advanced filter also has an option for
filtering out unique records BUT it's just that; unique RECORDS. That
is to say, if you have OTHER columns containing data (and I'm assuming
that you do), those would have to be the same as the ones before them
as well for that to work. With the way that I've described above, the
filter will identify unique items in a single column.
