Extract remaining hours from reversal entries

[vakubia0]

Hi,

I have a report I've downloaded from our accounting system. This report contains peoples hours booked to a job number and hours reversed from the job number. I need to be able to extract the remaining hours on the job. Meaning - delete the in and out entries and leave the rest. A Subtotal per person gives me the result of what is outstanding, but which row(s) contains that result.(found using vba code). do I need the subtotal?

Job Emp #/Doc # Service Date G/L Date Hours
43157200 925156 13-01-06 13-01-06 39
43157200 925156 09-01-06 20-01-06 -8
43157200 925156 10-01-06 20-01-06 -8
43157200 925156 11-01-06 20-01-06 -8
43157200 925156 12-01-06 20-01-06 -8
43157200 925156 13-01-06 20-01-06 -7
925156 Total 0
43157200 927080 08-07-05 08-07-05 2
43157200 927080 08-07-05 15-07-05 -2
43157200 927080 19-08-05 19-08-05 3
43157200 927080 02-09-05 02-09-05 10
43157200 927080 23-09-05 23-09-05 4
43157200 927080 16-08-05 30-09-05 -3
43157200 927080 29-08-05 30-09-05 -2
43157200 927080 30-08-05 30-09-05 -8
43157200 927080 21-09-05 30-09-05 -3
43157200 927080 22-09-05 30-09-05 -1
43157200 927080 10-02-06 10-02-06 13
43157200 927080 04-11-05 04-11-05 2
43157200 927080 11-11-05 11-11-05 4
43157200 927080 07-11-05 25-11-05 -4
43157200 927080 31-10-05 09-12-05 -2

927080 Total 13

In the first subtotal, everything equates to Zero, so all rows can be deleted.
In the second subtotal, we have 13 hrs outstanding. Need to be able to delete all the rows but the one containing 13. It seems that postive entry goes in on an particular service date, the negative entry gets a diff GL date, but its service date is less or equal to the positive entry service date, but the hours are not nessarily a exact negative of what went in.

Have I lost you.

43157200 927080 08-07-05 08-07-05 2 1st cancel out
43157200 927080 08-07-05 15-07-05 -2 1st cancel out
43157200 927080 19-08-05 19-08-05 3 2nd cancel out
43157200 927080 02-09-05 02-09-05 10 2nd cancel out
43157200 927080 23-09-05 23-09-05 4 2nd cancel out
43157200 927080 16-08-05 30-09-05 -3 2nd cancel out
43157200 927080 29-08-05 30-09-05 -2 2nd cancel out
43157200 927080 30-08-05 30-09-05 -8 2nd cancel out
43157200 927080 21-09-05 30-09-05 -3 2nd cancel out
43157200 927080 22-09-05 30-09-05 -1 2nd cancel out
43157200 927080 10-02-06 10-02-06 13 keep
43157200 927080 04-11-05 04-11-05 2 3rd Cancel out
43157200 927080 11-11-05 11-11-05 4 3rd Cancel out
43157200 927080 07-11-05 25-11-05 -4 3rd Cancel out
43157200 927080 31-10-05 09-12-05 -2 3rd Cancel out

how do I write in code matching up the cancels and delete them? the codes needs to tag the ones to cancel out and then a delete will get rid of them, leaving the remaining hours.

Any Ideas? A curly one.....

 

[Bong]

It seems that more than just the hours remaining are important to you; you seem to want the entry that has not been used up (or is used at all?). What if your 2nd employee had another entry with a -3 hours. The remaining would be 10 hours but there would be no entry for +10 hours. Would you want the 10 hours reported or the 13?

_________________
Bob Rashkin

 

[SkipVought]

Hi,

FIRST, you are working with a REPORT. Yuk!

Get rid of the subtotals by SORTING the table and deleting those EXTRANEOUS rows.

SECOND, never delete data (as opposed to deleting extraneous reporting stuff). Rather EXCLUDE unwanted data using some criteria. ALL the data is important at some time and for some reason -- maybe not for THIS particular time and reason.

Now your data can be analyzed and reported on just like a database table using many of Excel's data analysis and data reporting features.

I'd use MS Query -- Data/Get External Data/New Database Query - Excel Files -- YOUR WORKBOOK.....

You can query to report the aggregates you desire quite easily. Probably could be done (sort/delete/query 'n' all) in less than 5 minutes.

Skip,

_{[red]Be Advised![/red] A man who jumps from a bridge in Paris, is…
INSANE!}