Help with VBA time lookup Macro

[moxman]

Hi,

I am fairly new with VBA. I can work with simple macro functions but this one is a doozy.

I have a sample workbook attached and, The raw data contains timelogs and i need in figuring out a way to find the time in Column F in relation to the Personal Break in column G.
I have multiple variables for the lookup which made it difficult for me
Name (Column C)
Date and Time (Column F)
Type (Column G)

On the workbook, I have created a pivot table that gives the desired result somewhat but is not accurate to a tee. If the person went out at lets say 9:49AM and went back at 10:01AM The pivot will show them as separate actions instead of one.

So a macro can help, Can someone help me pull the data of Personal Break IN to match the Personal Break out? Put blanks instead if the person did not time in/out.
On the sample workbook, I have the RAW Data on one tab and the desired result on the OUTPUT tab.

Thank you.

[Keebellah]

Hi, welcome to the forum.
I haven't downloaded your file but at first if you read time values they'll always be different. You could add a helper column that if used in the pivot table would group the data.
E.g. add a column next to the time and just show the hour you will be able to filter on hours, the same with minutes
Hope I'm making sense

[moxman]

Hi, welcome to the forum.
I haven't downloaded your file but at first if you read time values they'll always be different. You could add a helper column that if used in the pivot table would group the data.
E.g. add a column next to the time and just show the hour you will be able to filter on hours, the same with minutes
Hope I'm making sense

Hi, Thank you for response.
I had created a helper column to filer the hours but what's bogging my mind is when the hour shifts to another hour, the pivot cant match the Ins and Outs together.

Totally lost at this one. Any suggestions would be much appreciated

[moxman]

I created another helper column, this time, i am counting the number of instances per day.
It was better than the hourly filter but it still wont match the correct time.
Is there a way for me to lookup and match these to the closest proximity?
Missing Entry.PNG

[Keebellah]

Nah, that won't work, I just looked at your file and well it isn't easy.
First, where does the data source come from?
Is that a punch clock or similar?
You will have to do quite some coding and the idea would be to not do it by hour but maybe person and date.
If anyone of us goes into this it will be loads of code.
Maybe use powerquery, I haven't used it but it seems to make life simple.
I'll see if I can come up with something but don't hold your breath

[moxman]

Nah, that won't work, I just looked at your file and well it isn't easy.
First, where does the data source come from?
Is that a punch clock or similar?
You will have to do quite some coding and the idea would be to not do it by hour but maybe person and date.
If anyone of us goes into this it will be loads of code.
Maybe use powerquery, I haven't used it but it seems to make life simple.
I'll see if I can come up with something but don't hold your breath

Thank you for helping out. I really ran out of ideas in this one.
The timestamps came from the Timekeeping tool which uses excel time to record the breaks.

[Keebellah]

The thing is you would have to add the events in the same row
Let's say if you go into MEETING and after a certain period you go Out of the meeting you would have to add that in as an extra two columns on the same row of the IN to meeting.
So the macro would have to analyse a start of an event an look for the next end of that event and add that to that row.

Load the sheet with raw data and then add a sheet to create the logs for IN and out.
It's quite some coding you'll have to do.
There is post with the word 'PunchClock' in the title. search for it. It's not the the same but it does create an excel file for the start adn when you do it for the same user again the end.
In your case your have more actions per staff member but maybe it gives you an idea

[Keebellah]

I wrote a macro for you.
Run the macrocreateLogReport

[moxman]

Thank you @Keebellah. you're the man!

[Keebellah]

Thanks for the compliment, Happy it helped you.
Don't forget to mark the post as SOLVED