Hello All,
I need some design help.
What I have is a need to repair the time card entries for a group of staff. What I mean by that is if someone makes an error in their time card entry this application (VBA or Excel worksheet function) will flag this error, and ask the person to correct it.
I have this example:
So we have in Column A the Network ID of the person who made the entry. Column B is the type of entry they made, and Columns C and D are the date and time the entry was made. Column E is a free form comments.
We can see that Adrianna had a perfect record. Each "Start" claim has a corresponding "End" claim.
But Alfredo is missing a "Break End" entry. I would need to flag this error and call Alfredo and have him correct this by adding a "Break End" entry.
Amy is missing a "Lunch Start" entry.
The challenges are that:
A) Not each person will have the same number and/or type of entries, as you can see this in the example.
B) This is a short woman-made segmented section. But I would like to make each Network ID a Named Range for further processing. I tried this formula:
=IF(($A$2:$A$18135=A2)*1>0,A2,"")
which spills and gets me the FIRST block of the same Network ID, but it does not work across the range. Yes I do have 18,135 rows to process!!! so this is why automation is needed.
Thanks for any advice.
Oh I tried to make the "Opposite entry" and search for that using this formula:
=IF(ISNUMBER(FIND(" End",A2,1)),SUBSTITUTE(A2," End"," Start"),SUBSTITUTE(A2," Start"," End")) which switches a "Start" to an "End" and an "End" to a "Start" and searching for it to "marry" it to the entry I am checking but that requires challenge B to be in place so that you don't get some other Network ID's entry.
Georgie Anne
