Hi,
First time posting so hope I cover everything. I'm boggled by this request that has come across my desk.
I have a large data set of leave entries in a system. Example of two employees in this example
I am wanting a formula driven solution that will identify if the red rows have been 'overridden' by the green rows so I can mark and flag the red rows for removal at a later time.
For example, Row 7 (John Smith, Tentative, 8am 20th June - 5pm 23rd June) and Row 8 (John Smith, Tentative, 8am 27th June - 5pm 27th June) are both overridden by Row 6 (John Smith, Accepted, 20-27 June) because the dates are within the larger date range of F6:G6
Unfortunately we cannot rely on just removing all Tentative status rows as tentative can still become accepted when a new extract is taken. The key is to lookup all data and determine if there is a row belonging to the same employee with an 'Accepted' status and dates that are both before and after this entry.
So H7 needs to determine if the dates in H5:H6 falls anywhere between F:G with the criteria of matching the payroll number and status=Approved.
It may fall in the too hard basket unless one of you wizards can suggest a solution. Thanks in advance!
First time posting so hope I cover everything. I'm boggled by this request that has come across my desk.
I have a large data set of leave entries in a system. Example of two employees in this example
I am wanting a formula driven solution that will identify if the red rows have been 'overridden' by the green rows so I can mark and flag the red rows for removal at a later time.
For example, Row 7 (John Smith, Tentative, 8am 20th June - 5pm 23rd June) and Row 8 (John Smith, Tentative, 8am 27th June - 5pm 27th June) are both overridden by Row 6 (John Smith, Accepted, 20-27 June) because the dates are within the larger date range of F6:G6
Unfortunately we cannot rely on just removing all Tentative status rows as tentative can still become accepted when a new extract is taken. The key is to lookup all data and determine if there is a row belonging to the same employee with an 'Accepted' status and dates that are both before and after this entry.
So H7 needs to determine if the dates in H5:H6 falls anywhere between F:G with the criteria of matching the payroll number and status=Approved.
It may fall in the too hard basket unless one of you wizards can suggest a solution. Thanks in advance!
