Find and complete missing times in a huge list

Hi all,

I wonder if someone could help me please?

I have a large list of data (often 30,000+ rows) and I have to make sure that for each month there is a row of data for each name and for each daypart segment. If a daypart segment if missing for a name then I need to add it by inserting a row in the appropiate place, enter the missing data and put 0 as a total figure. Attached is a workbook which I hope will help to understand!

For example, in the attached workbook the first problem is on row 33 (highlighted in yellow). There is a missing daypart segment, Name2 has no 06:00:00 - 08:59:59 data.

In my head I was thinking of a code like this:

Please Login or Register  to view this content.

There are several problems with this.
First of all it doesn't work!
Second I am worried as it is using a loop on several throusand rows and this may take a very long time(?)

The daypart segments change and the number of daypart segments but are always defined in the next sheet, the heading names are always the same and in the same position and the data is always in the order as the daypart segments in the next sheet.

This a big problem which I often come across in my new role so would REALLY appreciate any help that can be given!

Thank you!

Hi Marvin,

Thanks for your reply.

My problem is that I have to know which daypart segments, names etc are missing and put in zeros - it is to enter the data into a different system so it all has to be accounted for or else it won't go in.

I could extend the pivot table so I can find out which dayparts / names are missing etc but it will still be quite manual and time consuming to add these all in.

Hmm... I'm really stuck!

Thanks for any advice.

Is the attached what you mean? It will be really helpful to see which are missing but to go through and check and then manually write the missing ones out before adding to the list on the left will still be quite time consuming.

However you have given me another idea.

If we managed to get a list of months, a list of names and a list of time segments we could build a list of full segments etc ourselves. Then we could use vlookup to add the totals in from the list we have and put zeros in any blank cells?

I have added another sheet onto the attached. If we have a list of the unique values of the year and month (any missing months don't matter as we have no data for these), unique list of names and unique list of start times, could we have a macro saying something like this?

a = no. of daypart segments
b = no. names
c = no. months

Copy the list of daypart segments in column D into column I (b*c) times.
This gives us the dayparts we need.

Copy name1 from column C into column H (a) times.
Repeat for all names in column C.
Copy the list we now have in column H (c) times.
Now we have all the names and corresponding dayparts.

Copy month1 from column B into column G (a*b) times.
Repeat for all names in column B.

This should give us the complete table that we need so all we have to do is populate it with total figures.
The ones we have data for we can use v-lookup to match the old table and any which are not found in the old table we can enter zero in.

What do you think? Is this possible and is it faster than the way I previously suggested do you think?

Thank you, I really do appreciate it!

Hi Marvin,

I understand why you got lost!
It's a really annoying system we have at work that will not allow any gaps in the data and so we have to have a full list to get it into the system.

You've given me some great ideas, I will go away with the new ideas you have given me and try to write a macro myself and then come back.

Thank you!!

see attachment. It looks like what you wanted to achieve.
Make sure the following reference is on:
1. [Alt+F11] - open Visual Basic window
2. Tools - References - find and tick "Microsoft Activex Data Objects 2.0 Library"
3. Press "Ok"...
Run macro "Huge"