Insert rows based on start/end values

**JP Romano** · 12-29-2011, 01:29 PM

Hello all - happy almost New Years!
Working on a little project for an associate, who is tracking completed work items for vendors in various parts of the world. I'm having some trouble getting the data to cooperate, and thought clearer minds might be of some assistance.

I've attached a data sample, with the data and schedule on sheet 1, and what I want the end result to look like on the "SampleResults" tab.

The goal is to insert a row for each user (on each day), with a "0" Instance Count, for each hour that falls within the schedule (start hour / end hour) where there are no completed work items logged.

For example, if User 1 is scheduled to work from 6 to 15, and has completed work items for 6-12 and 14-15, I need a line for hour 13 added for that day. If she has completed work items before or after scheduled hours, they should be left alone, and no lines added before or after. So in the same example, if User 1 had another work item completed at 17, we do not need to add another line for hour 16.

Hopefully the attachment will make it a little more clear.

Thanks for any help!

**p24leclerc** · 01-03-2012, 11:15 PM

I don't understand the results you have on rows 35-45 compared to rows 47 to 58.
Both miss a completed work item at 14 hours but you added a row only for the last sample (47-58).
I think you should have one row inserted between rows 42 and 43. Am I right or did I missed something?
Regards
Pierre

**JP Romano** · 01-04-2012, 10:31 AM

Ugh... sloppy, sloppy sloppy.
Yes, you're right... I missed one between 42 and 43... there should be a row for hour 14, with 0 work items. Thanks for pointing that out!

**p24leclerc** · 01-06-2012, 09:52 AM

Here is a code that should work. At least it does with the sample date you supplied. Also, look at attached file.

Please Login or Register  to view this content.

**JP Romano** · 01-06-2012, 02:06 PM

Holy crap, that's awesome! Thank you so much...
One little monkey wrench that I was unaware of - and if this is a big deal, don't even give it another thought.
I JUST found out that some of the users may work overnight - so instead of having hours like 9 - 17, they'll have hours like 18-2. Would that be terribly difficult to accommodate?

**p24leclerc** · 01-06-2012, 07:06 PM

Hi,
I realised there was a small glitch in the program so I corrected it. It was occuring when there was a gap of more than 1 hour between the last row and the above row.

I also think I'm able to process night shift data. I programmed a function (Night_S) which is called at the beginning of the program. It modifies the day and time in Sheet1 in order to reduce the day by 1 and add 24 hours to the hours over midnight. This way the program knows it has to process this line as part of the actual user.

Give it a try and let me know how it performs.
Pierre

Please Login or Register  to view this content.

Pierre

**JP Romano** · 01-09-2012, 02:47 PM

Very sorry for the delay - I hope you don't think I'm not incredibly grateful - was offline this weekend and just now got to this code. Will give it a shot today and let you know how it works out!
Thank you again for your time and help!

**JP Romano** · 01-09-2012, 03:08 PM

I have a slightly different structure, based on user requirements/requests, and am having a little bit of trouble adapting the code (which is a bit over my head, honestly). If it's not too much trouble, and you have the bandwidth, could you please take a look? Your code is in module 4, and I think the problem is that the actual hours worked are in a different place than where your code is looking. I could be wrong, of course!

Thank you so much for your patience and time - I'll continue to try to figure it out as well, but do appreciate any additional help...

**JP Romano** · 01-09-2012, 03:26 PM

Okay, I think I figured it out... solving threat... thank you so much!

**p24leclerc** · 01-09-2012, 10:41 PM

I also limited the U_sers array to 10 users. If you ever need more than that, just change the DIM statement to fit your needs.

Insert rows based on start/end values

LinkBack

Thread Tools

Rate This Thread

Display

Insert rows based on start/end values

Re: Insert rows based on start/end values

Re: Insert rows based on start/end values

Re: Insert rows based on start/end values

Re: Insert rows based on start/end values

Re: Insert rows based on start/end values

Re: Insert rows based on start/end values

Re: Insert rows based on start/end values

Re: Insert rows based on start/end values

Re: Insert rows based on start/end values

Thread Information

Users Browsing this Thread

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions