Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Locating duplicates within range of time punch data
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! -- heyredone |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Locating duplicates within range of time punch data
Hi
How many worksheets do you have? Is it possible that the duplicated entries are located on two or more worksheets? pa "heyredone" wrote: 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! -- heyredone |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Locating duplicates within range of time punch data
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:D$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. -- If this helps, please click the Yes button Cheers, Shane Devenshire "heyredone" wrote: 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! -- heyredone |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Finding duplicate time punch entries | Excel Discussion (Misc queries) | |||
locating duplicates | Excel Discussion (Misc queries) | |||
Cannot extend the range of data I'm trying to prevent duplicates i | Excel Worksheet Functions | |||
Locating variable range to copy | New Users to Excel | |||
Locating a value in a range from a reference. | Excel Worksheet Functions |