Cleaning Data - HELP!

[sawdeeka]

Hi,
I need urgent help to develop a formula for the following:

I have an extremely large data set, approximately 600,000 rows. There are a number of variables that need to be considered but basically I need to delete positive and negative numbers (hours paid) per employee (employee id), per date (date of leave). I am pretty good with excel but this one is hurting! Appreciate any suggestions!


[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Employee ID[/TD]
[TD]Leave Type[/TD]
[TD]Date of Leave[/TD]
[TD]Hours Paid[/TD]
[/TR]
[TR]
[TD]123[/TD]
[TD]Sick Leave[/TD]
[TD]01/01/2000[/TD]
[TD]7.5[/TD]
[/TR]
[TR]
[TD]123[/TD]
[TD]Sick Leave[/TD]
[TD]01/01/2000[/TD]
[TD]-7.5[/TD]
[/TR]
[TR]
[TD]123[/TD]
[TD]Rec Leave[/TD]
[TD]02/02/2001[/TD]
[TD]10.00[/TD]
[/TR]
[TR]
[TD]456[/TD]
[TD]Rec Leave[/TD]
[TD]13/02/2001[/TD]
[TD]8.00[/TD]
[/TR]
[TR]
[TD]456[/TD]
[TD]Sick Leave[/TD]
[TD]13/02/2001[/TD]
[TD]-8.00[/TD]
[/TR]
[TR]
[TD]789[/TD]
[TD]Family Leave[/TD]
[TD]01/01/2000[/TD]
[TD]8.00[/TD]
[/TR]
[TR]
[TD]789[/TD]
[TD]Family Leave[/TD]
[TD]02/01/2000[/TD]
[TD]8.00[/TD]
[/TR]
[TR]
[TD]789[/TD]
[TD]Family Leave[/TD]
[TD]03/01/2000[/TD]
[TD]8.00[/TD]
[/TR]
[TR]
[TD]789[/TD]
[TD]Family Leave [/TD]
[TD]04/01/2000[/TD]
[TD]8.00[/TD]
[/TR]
</tbody>[/TABLE]

 

[eduzs]

you can apply a filter and delete the result
you can also use a vba macro
what would be the rules to delete or not ? You're going to delete the entire row or the cell value?
backup before doing any test

 

[sawdeeka]

you can apply a filter and delete the result
you can also use a vba macro
what would be the rules to delete or not ? You're going to delete the entire row or the cell value?
backup before doing any test

Hi, if I was to apply a filter wouldn't I need to do that per employee, per date? I would be looking to delete the entire row.

 

[sawdeeka]

Sorry I may not have been clear.

To confirm I would be looking to delete rows that relate to positive and negative entries (hours paid) for individual employees (employee id), on matching dates (date of leave).

Sorry I am not sure I am articulating correctly

 

[Akuini]

Are the positive and negative entries (that needs to be deleted) always adjacent to each other?

 

[sawdeeka]

Are the positive and negative entries (that needs to be deleted) always adjacent to each other?

Yes they appear to be.

The date range is over 12 months, and there are approximately 10,000 employees

 

[Michael M]

There seems to be more questions than answers !!
Can you post the above data set AFTER the lines are deleted /
and
Is the data sorted ?

 

[sawdeeka]

There seems to be more questions than answers !!
Can you post the above data set AFTER the lines are deleted /
and
Is the data sorted ?

Sorry not too sure I follow, post the data set after the lines are deleted?

I have custom sort the data by Employee ID (smallest to largest) and Date of Leave (oldest to newest)

 

[Akuini]

Ok, assuming the positive and negative entries (that needs to be deleted) always adjacent to each other, try this on a small sample first:
I use col E as a temporary helper column, you may change that to suit

[FONT=lucida console][COLOR=Royalblue]Sub[/COLOR] a1107635a()
[I][COLOR=seagreen]'https://www.mrexcel.com/forum/excel-questions/1107635-cleaning-data-help.html[/COLOR][/I]
[COLOR=Royalblue]Dim[/COLOR] i [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]Long[/COLOR], n [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]Long[/COLOR]
[COLOR=Royalblue]Dim[/COLOR] va, vb
n = Range([COLOR=brown]"A"[/COLOR] & Rows.count).[COLOR=Royalblue]End[/COLOR](xlUp).Row
va = Range([COLOR=brown]"A1:C"[/COLOR] & n)
vb = Range([COLOR=brown]"D1:D"[/COLOR] & n)

[COLOR=Royalblue]For[/COLOR] i = [COLOR=crimson]2[/COLOR] [COLOR=Royalblue]To[/COLOR] n
[COLOR=Royalblue]If[/COLOR] vb(i, [COLOR=crimson]1[/COLOR]) < [COLOR=crimson]0[/COLOR] [COLOR=Royalblue]Then[/COLOR]
    [COLOR=Royalblue]If[/COLOR] vb(i - [COLOR=crimson]1[/COLOR], [COLOR=crimson]1[/COLOR]) = -vb(i, [COLOR=crimson]1[/COLOR]) [COLOR=Royalblue]And[/COLOR] va(i - [COLOR=crimson]1[/COLOR], [COLOR=crimson]1[/COLOR]) = va(i, [COLOR=crimson]1[/COLOR]) [COLOR=Royalblue]And[/COLOR] va(i - [COLOR=crimson]1[/COLOR], [COLOR=crimson]3[/COLOR]) = va(i, [COLOR=crimson]3[/COLOR]) [COLOR=Royalblue]Then[/COLOR]
         vb(i - [COLOR=crimson]1[/COLOR], [COLOR=crimson]1[/COLOR]) = [COLOR=brown]"True"[/COLOR]: vb(i, [COLOR=crimson]1[/COLOR]) = [COLOR=brown]"True"[/COLOR]
    [COLOR=Royalblue]ElseIf[/COLOR] vb(i + [COLOR=crimson]1[/COLOR], [COLOR=crimson]1[/COLOR]) = -vb(i, [COLOR=crimson]1[/COLOR]) [COLOR=Royalblue]And[/COLOR] va(i + [COLOR=crimson]1[/COLOR], [COLOR=crimson]1[/COLOR]) = va(i, [COLOR=crimson]1[/COLOR]) [COLOR=Royalblue]And[/COLOR] va(i + [COLOR=crimson]1[/COLOR], [COLOR=crimson]3[/COLOR]) = va(i, [COLOR=crimson]3[/COLOR]) [COLOR=Royalblue]Then[/COLOR]
         vb(i + [COLOR=crimson]1[/COLOR], [COLOR=crimson]1[/COLOR]) = [COLOR=brown]"True"[/COLOR]: vb(i, [COLOR=crimson]1[/COLOR]) = [COLOR=brown]"True"[/COLOR]
    [COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]If[/COLOR]
[COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]If[/COLOR]
[COLOR=Royalblue]Next[/COLOR]

[I][COLOR=seagreen]'Col E as helper column[/COLOR][/I]
Range([COLOR=brown]"E:E"[/COLOR]).ClearContents
[COLOR=Royalblue]With[/COLOR] Range([COLOR=brown]"E1:E"[/COLOR] & n)
.Value = vb
[COLOR=Royalblue]On[/COLOR] [COLOR=Royalblue]Error[/COLOR] [COLOR=Royalblue]Resume[/COLOR] [COLOR=Royalblue]Next[/COLOR]
.SpecialCells(xlConstants, xlLogical).EntireRow.Delete
[COLOR=Royalblue]On[/COLOR] [COLOR=Royalblue]Error[/COLOR] [COLOR=Royalblue]GoTo[/COLOR] [COLOR=crimson]0[/COLOR]
[COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]With[/COLOR]
Range([COLOR=brown]"E:E"[/COLOR]).ClearContents

[COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]Sub[/COLOR][/FONT]

 

[Michael M]

Simple question really....post the sample dataset, in the first post AFTER your lines had been deleted.
That way we see a before and after result...

But I see @Akuini has given you a solution...