Find duplicates from 4 columns in excel sheet

  • Thread starter Thread starter RompStar
  • Start date Start date
R

RompStar

Howto locate duplicates in excel sheet

Ok, I have a excel sheet with 4 columns:

Department, Date, Employee Name, Absent Code

Anyways, each day different department use a front end
sheet that I made, pick their list selections and press upload...

The data range gets uploaded to a running file that stacks
data under the old data without spaces... Headers in this
running file are A1:D1

Everything below it is my data, current range is:

A2:D4346

What I wanted to do with this file is to analyze it for double posts.
In the upload script I simply date stamp a protected cell and compare
that, only allowing 1 upload a day, but sometimes, there are weird
things happening, and the data uploads twice, so i wanted to search for
them, at first just to search for them and maybe highlight the affected
fields..

Anyways, does a build-in tool come with Excel for this or am I going to
have to figure out how to do this with VB ?

So I guess maybe use the Name as a point to compare agaist, because a
persons name should only apear once for a Date, if it appears twice,
then that could be a duplicate.

Thanks.
 
I'd use a couple of helper columns.

In column E2 (headers in row 1?)
=A2&"--"&B2&"--"&C2&"--"&D2
(if you don't like the date looking funny, you could use:
=A2&"--"&text(B2,"mm/dd/yyyy")&"--"&C2&"--"&D2

And drag down all 4346 rows.

Then put this in F2:
=COUNTIF(E:E,E2)
And drag down all 4346 rows.

Then apply data|filter|autofilter to that range. If the value is 1, it's
unique. So you can just show the ones that are greater than 1.
 
thanks, I will work with this when I find some free time, thanks again
for your help, if I get stuck, I will post in a few days, so
many things to do :- )
 
Back
Top