PDA

View Full Version : [SOLVED] VBA to check and delete the entire row



shiva_reshs
09-13-2018, 09:57 PM
Hi,

I am trying to find logic to build a code around this. but could not put my brain into it. below is the data



ID
Time Off Date
Leave Type
Unit of Time

Units


1234
8/7/2018
Annual
Days

1


1234
8/7/2018
Annual
Days

-1


1234
8/7/2018
Annual
Days

1


2287
8/7/2018
Sick
Days

1


7276
8/7/2018
Annual
Days

1


7276
8/7/2018
Annual
Days

-0.5



1) If ee has taken leave(Same leave type) with one unit and on same day if there is a withdrawal then delete both the row. However in some cases you would see, employee has taken, then withdraw and reapplied. In such scenario, just delete one plus and one negative record and keep the 3rd Positive or negative record as it is.

2) Another scenario, employee has applied for 1 day leave(Same leave type) and later on same day he withdraw -0.5 leave. In such scenario, only keep one record replacing the 1 days with +0.5.

3) Third scenario, employee has applied for 0.5 day leave(Same leave type) and later on same day applied another 0.5. In such scenario, sum up the and keep one record.

Can this logic be build in vba? please help. Been stuck on this question since last night trying to figure out how it can be done?

Note :1) This is for bulk list of employee which is random every month.
2) Deletion and addition is only when same day leave and same leave type. Else ignore
Thanks

p45cal
09-14-2018, 08:51 AM
In the attached is a very straightforward pivot table taking less than a minute to set up.

I've added a new ID 9999 at the bottom to show how if a user has entered data which cancels itself out (adds to zero) it can be eliminated from the list instead of showing a zero. See the filter for ID in the pivot table.

shiva_reshs
09-18-2018, 07:30 PM
In the attached is a very straightforward pivot table taking less than a minute to set up.

I've added a new ID 9999 at the bottom to show how if a user has entered data which cancels itself out (adds to zero) it can be eliminated from the list instead of showing a zero. See the filter for ID in the pivot table.

Wow, works perfect. Thanks a lot.