JMHenriques
New Member
- Joined
- Aug 4, 2015
- Messages
- 3
Hello everyone,
I used excel a lot but for the last couple of years not so much and it seems I'm a little to dumb for this
Basically I have raw data from a time management program that when corrections are made new records are created, first the number of hours in the original entry is subtracted, and them a new row is added with the new information.
So I would get the following output from a single correction.
In the example above we see that the first entry in yellow for Name "John Doe" made on the DATE_Time_Writing "3/1/2021" with NUMBER_OF_HOURS of "3.00" had a correction made on the 20th of April (column TIME_STATUS_DATE) were it was subtracted the 3 hours. Them, in the last line in green we see the new record with the 3 hours with the missing information in column "Overtime Details".
What I need is a helper column like in the example that tell me the rows to keep and to delete... I'm trying a lot of different approaches using arrays and so on but until now with no luck... I know I have to somehow check if the DATE_Time_Writing is the same for the same Name and them check if there was a subtraction made and also... confirm the new corrected entry... but cannot see a possible logic to do this...
If anyone could help me out with a possible solution or just point me in the right direction it would be wonderful
I used excel a lot but for the last couple of years not so much and it seems I'm a little to dumb for this
Basically I have raw data from a time management program that when corrections are made new records are created, first the number of hours in the original entry is subtracted, and them a new row is added with the new information.
So I would get the following output from a single correction.
| CheckIfValid.xlsx | |||||||||
|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | |||
| 1 | NUMBER_OF_HOURS | TYPE_OF_HOURS | TIME_STATUS_DATE | Overtime details | DATE_Time_Writing | Name | Helper column | ||
| 2 | 3.00 | Overtime weekday | 29 March 2021 | 3/1/2021 | John Doe | Delete | |||
| 3 | 5.00 | Overtime weekend | 29 March 2021 | 3/1/2021 | John Doe | Keep | |||
| 4 | 8.00 | Regular time | 29 March 2021 | 3/1/2021 | John Doe | Keep | |||
| 5 | -3.00 | Overtime weekday | 20 April 2021 | 3/1/2021 | John Doe | Delete | |||
| 6 | 3.00 | Overtime weekday | 20 April 2021 | Overtime reason… | 3/1/2021 | John Doe | Keep | ||
After | |||||||||
In the example above we see that the first entry in yellow for Name "John Doe" made on the DATE_Time_Writing "3/1/2021" with NUMBER_OF_HOURS of "3.00" had a correction made on the 20th of April (column TIME_STATUS_DATE) were it was subtracted the 3 hours. Them, in the last line in green we see the new record with the 3 hours with the missing information in column "Overtime Details".
What I need is a helper column like in the example that tell me the rows to keep and to delete... I'm trying a lot of different approaches using arrays and so on but until now with no luck... I know I have to somehow check if the DATE_Time_Writing is the same for the same Name and them check if there was a subtraction made and also... confirm the new corrected entry... but cannot see a possible logic to do this...
If anyone could help me out with a possible solution or just point me in the right direction it would be wonderful
