Results 1 to 14 of 14
  1. #1
    New Lounger
    Join Date
    Jan 2013
    Posts
    8
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Help with BIG Project

    Hello all.

    I'm new member here and I found out about this forum when I was surffing around net to find out solutions for my problem. So let me explain a little. As in picture you'll see an basic excel sheet. What I need to do for every 6 weeks is to create these daily excel's for every 42 days (per 6 week). And it tooks lot of time to move all the names manually. The picture is just example, in real excel I have aprox. 150 names per day and 10-15 different functions and all different starting and ending times.

    Problem_excel.jpg

    So what I'm asking now, is there a formula or something, that I could just but the namelist per day in some column and it could automaticly move the names in right positions? And it also have to somehow lookup that what times that persons has and move it to matching group with same time. And of course there might be many groups working with same times so it should only have maximum 4 people with function 1xFM 1xGEM and 2xLD (or 3xLD and 0xGEM).

    I think this explanation is bit hard to understand, but if somebody will have anu qlue, what I'm asking, I can explain a little more.

    Thanks all for your help, if you can help.

  2. #2
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,639
    Thanks
    115
    Thanked 651 Times in 593 Posts
    See if this is what you are trying to do.

    HTH Maud
    Attached Files Attached Files

  3. The Following User Says Thank You to Maudibe For This Useful Post:

    Otto2010 (2013-01-03)

  4. #3
    New Lounger
    Join Date
    Jan 2013
    Posts
    8
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Hey,

    thanks a lot, that is exactly what I was looking for. But just to let you know, that was just an example, could check the real excel also or let me know how did you do that one? Because the real excel is not that easy with all the 100+ names and 15+ different functions etc...

    But anyway, thanks already.

    Toni

  5. #4
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,639
    Thanks
    115
    Thanked 651 Times in 593 Posts
    Post the excel file and I will fit the code. By the way, Welcome to the forum.
    Maud
    Last edited by Maudibe; 2013-01-03 at 10:34.

  6. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    A pivot table created from the datatable may be a simple way, without coding, to compile and even summarize/analyze the data including other columns.

    Steve

  7. #6
    New Lounger
    Join Date
    Jan 2013
    Posts
    8
    Thanks
    1
    Thanked 0 Times in 0 Posts
    I will Post it here later. I will Post the original and also that One where we these days start to build it. And maybe a little more of explaining.

  8. #7
    New Lounger
    Join Date
    Jan 2013
    Posts
    8
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Excels to project

    Hi all,

    so here's my 3 different excels. Excel1 is the original excel file after I have done the Schedule Planning. Only times and functions etc... no names. Excel2 has list of names for 1 day after I'v published people's schedules. And excel3 & 4 is final output at the moment. And pls remember, that at the moment the final output is done manually from that namelist (excel2) And it tooks around 30-36 hours to do manually all those 42 days in row. And then after 6 weeks are gone, do it again for the next 6 weeks. I think you all get the idea, why I'm trying to find a solution to do it better way.

    Let me know if you need to know more explains...

    Thanks a million
    Attached Files Attached Files

  9. #8
    New Lounger
    Join Date
    Jan 2013
    Posts
    8
    Thanks
    1
    Thanked 0 Times in 0 Posts
    And 1 thing I forgot, all the days are little different as you might see. So the days are not identical...

  10. #9
    New Lounger
    Join Date
    Jan 2013
    Posts
    8
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Do you think that those my excels and the thing I'm trying now could be done with pivot table etc. ?

  11. #10
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,639
    Thanks
    115
    Thanked 651 Times in 593 Posts
    Although pivot tables are not my thing, I am sure I will be able to help you with code. I have several questions:
    1. Excel2 and Excel3, are these daily reports, weekly reports, monthly, or every 6 weeks? In other words, how often do you have to complete one of these froms?
    2. Will the names list (excel2) always be the same along with their function IDs?
    3. As in my return sample, only the employees with function IDs (FM, GEM, and LD) will be sorted under the time slots according to their shifts on the name list, correct?
    4. The rest of the employees are filed under the other categories (Allocs/Tracers, DSM, Toilet-Service, etc) acording to their finction IDs?
    5. Will the categories always be the same or do they change from reporting period to reporting period? They seem the same Excel3 and Excel4.
    6. Excel4 lists a Special Foreman, Kempas Mikko, but he is not on the employee list. Please explain
    7. Does the sheet have to remain in the same format or can the categories be move to a separate sheet from the time slots?
    8. Is there a reason why the names list is a separste workbook? Can it be in the same workbook but on a different sheet (Tab)?
    9. What does the "E" and the "M" represent in the left columns of the time slots?
    If you can answer these, I will start to help you.
    Maud
    Last edited by Maudibe; 2013-01-03 at 20:54.

  12. #11
    New Lounger
    Join Date
    Jan 2013
    Posts
    8
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Good morning. Sorry for late answer, had to sleep couple of hours, it's 6 am in Finland. But I'll try to answer your questions. In RED after your questions
    Quote Originally Posted by Maudibe View Post
    Although pivot tables are not my thing, I am sure I will be able to help you with code. I have several questions:
    1. Excel2 and Excel3, are these daily reports, weekly reports, monthly, or every 6 weeks? In other words, how often do you have to complete one of these froms?So the Excel2, the name list, is taken from the other system, once in 6 week period and it will have 42 sheets of names. 1 day in 1 sheet. And from that we create all those 42 (Mon to Sun for 6 week period) daily excels as excel3 and 4. So we do it once in 6 week for the next 6 week period.
    2. Will the names list (excel2) always be the same along with their function IDsName list format is always the same, but the names of course are not. So we have more than 600 people working and something around 100-150 per day. And 1 person can more than 1 function id in 6 week period. Not in 1 day but like in same week same employee can have like monday SD and tuesday TOW etc.?
    3. As in my return sample, only the employees with function IDs (FM, GEM, and LD) will be sorted under the time slots according to their shifts on the name list, correct?That is correct.
    4. The rest of the employees are filed under the other categories (Allocs/Tracers, DSM, Toilet-Service, etc) acording to their finction IDs?This is is also correct. In these other categories they don't have that same kind of group system, but they are individuals working on their own under the same compartment.
    5. Will the categories always be the same or do they change from reporting period to reporting period? They seem the same Excel3 and Excel4.
    6. Excel4 lists a Special Foreman, Kempas Mikko, but he is not on the employee list. Please explain Categories are same (in right hand and bottom of that excel) every day. Well exept we dont need deicing in summer but anyhow. What comes to the Special Foreman Kempas Mikko, he's name is in namelist (excel2) but the name list is only for 1 day = to excel3. I sended the excel4 to just to notify, that the days are not identical. So for every day, we're having own namelist and own excel for the end result.
    7. Does the sheet have to remain in the same format or can the categories be move to a separate sheet from the time slots?The end result can be modified, but the final output should have to be like it is now in excel3 and 4, cause the final output is also printed in daily bases and but on wall that employee's see their groups etc...
    8. Is there a reason why the names list is a separste workbook? Can it be in the same workbook but on a different sheet (Tab)?Yes, the name list can be put in same excel. The original excel is made manually. The name list comes from other system as an excel sheet. And yes we can put it to same excel in different tab after that. Put this can be done manually.
    9. What does the "E" and the "M" represent in the left columns of the time slots?E means FM and M means GEM. It has always been like that don't ask me why. What I was thinking, that if its possible to move also the function id but only for those groups. I mean that then the current E will be FM and M will be GEM or it doesnt matter. Cause those are just daily markers, and they can be inputted there manually before printing it out.
    If you can answer these, I will start to help you.
    Maud
    And once again, thanks a lot.

    Toni

  13. #12
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,639
    Thanks
    115
    Thanked 651 Times in 593 Posts
    Good explanations. Yes, I can help you. Midnight here in East US. Will start tomorrow, finish maybe this weekend.
    Maud

  14. #13
    New Lounger
    Join Date
    Jan 2013
    Posts
    8
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Okay, that's fast. Well next time for doing those excels is in 2 weeks so if we have it then it would be super. If we don't we do it old way.

    Talk to you later.

    Toni

  15. #14
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,639
    Thanks
    115
    Thanked 651 Times in 593 Posts
    Toni,
    "The name list comes from other system as an excel sheet."
    1. Just to confirm, the names list generated from the other system is automatically entered into an excel spreadsheet, correct?
    2. Is it automatically placed in the same format as Excel2?
    3. Is the spreadsheet in the form of a saved file (.xls), printed, or both?
    4. Is Excel2 a sample of exactly what you receive from the other system? Ex. Header at top of sheet? (although names may be different)?
    5. Does Excel3 and Excel4 show all possible time slots (on the left side) or are there additional ones that are not in either of these?
    6. In Excel3 there 4 time slots 0630-1800, 07-10 & 14-18 (3), 1430-0030 (3), etc. Same in Excel4. How do you know which employees with these hours get grouped into time slots with same time? Please explain
    7. In Excel4, Sjoblom Kim, Kawada Kai, and Pakkala Marko, have time shifts next to the right of their names that are different from the time shifts they are under. What do they indicate?
    8. Placement for some of the function ID were not indicated in Excel1: PROP, RSPV. In what category to they belong?
    9. Excel4 Cell C40: What does DIRT 10-14 mean?
    10. In the time slots is FM always listed first? Second is GEM? What is the remaining order?
    11. What version of Excel do you use?

    Almost half done.
    Thanks,
    Maud
    Last edited by Maudibe; 2013-01-06 at 01:48.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •