Locating duplicates within range of time punch data

[heyredone]

I have numerous worksheets with employee daily time punch data over a period
of 4 years. I have discovered duplicate entries and want to weed (and
eventually delete) out the duplicates. Can I find the duplicates without
having to manually look at each line entry. Worksheet is set up as follows:

Columns:
A = First Name
B = Last Name
C = Badge No.
D = Store No.
E = ScanDttm (Date)
F = [blank on purpose]
G = Time [this translates to a time punch In or Out on a given date (Column E)

Eventually, I will create Columns H thru K for each person as "Time In",
"Time Out", "Time In" and "Time Out" so I can see time punches for a given
day left to right rather than downward. Some time back another Discussion
Group member provided me with a formula to use for this purpose that will
work beautifully.

I just need to weed out the duplicates first.

Thank you!

 

[PA]

Hi
How many worksheets do you have? Is it possible that the duplicated entries
are located on two or more worksheets?

pa

 

[Shane Devenshire]

Hi,

by duplicate entries you mean that all the data in all the columns matches?
If you are using 2007 you can remove all duplicates by choosing the Data,
Remove Duplicates command.

In 2003 you can use a formula approach to compare all the columns for every
row

=SUMPRODUCT(--(A1=A$1:A$1000),--(B1=B$1:B$1000),--(C1=C$1:C$1000),--(D1=D$1$1000),--(E1=E$1:E$1000),--(F1=F$1:F$1000),--(G1=G$1:G$1000))

Copy this formula down, anytime it returns a result greater than 1 you have
a duplicate.

You might also consider sorting.