Excel table to row conversion

[Ramz21]

Hi All,

Please help me on how to convert the source file into the destination file as I need to update the schedules and monthly basis

Regards,
Ram

 

[Somendra Misra]

Hi Ram,

How about putting some sample data also into both files.

Regards,

 

[Ramz21]

Hi SM,

Herewith I am attaching the files with sample data.

Regards,
Ram

 

[Somendra Misra]

@Ramz21

Now see it is more clear than your previous files.
Now some questions?

1. How many shifts are there say Morning, Afternoon and so on?
2. What is the criteria to decide what shift is what time?
3. Are blank cells going to be substituted with Weekly off always?
4. What are the total number of rows of data are you going to target?

Regards,

 

[Ramz21]

Hi SM,

Below are the answers for the questions

1. How many shifts are there say Morning, Afternoon and so on?
I have a shift every 4-5 hours
2. What is the criteria to decide what shift is what time?
this will be provided by the respective area manager

3. Are blank cells going to be substituted with Weekly off always?
Yes in most cases
4. What are the total number of rows of data are you going to target?
On a average each region contains 70-100 employees.

Thanks & Regards,
Ram

 

[Somendra Misra]

Hi SM,

Below are the answers for the questions

1. How many shifts are there say Morning, Afternoon and so on?
I have a shift every 4-5 hours
2. What is the criteria to decide what shift is what time?
this will be provided by the respective area manager

3. Are blank cells going to be substituted with Weekly off always?
Yes in most cases
4. What are the total number of rows of data are you going to target?
On a average each region contains 70-100 employees.

Thanks & Regards,
Ram

@Ramz21

Hmmm more confusion with your answers, specially no.2 if manager decides which hour will fall into which shift than in your file Shift_Summary column, how this will get updated automatically? It's not possible and you have to do manually.

And more things: Will these files going to be separate or in same file different sheet?

Will you have different source file and destination files for each region?



Regards,

 

[Ramz21]

Hi SM,

Looks like I misinterpreted your question. Sorry for the trouble.

Here is the answer.

6:00 AM to 1:00 PM it is Morning Shift,
1:00 PM to 5:00 PM Afternoon shift,
5:00 PM to 9:00 PM Evening shift,
9:00 PM to 6:00 AM Night shift

Let me know if this helps.

Regards,
Ram

 

[Ramz21]

Hi SM,

Currently, I am maintaining separately, however, if required I can incorporate in one singe file. Have different source files but ONE destination file.

Regards,
Ram

 

[Somendra Misra]

@Ramz21

You may have to wait by tomorrow to let me complete with the first cut.

Regards,

 

[Ramz21]

Hi SM,
Sure. Thank you very much.

Regards,
Ram

 

[Somendra Misra]

@Ramz21

See the file Sheet2 has solution, Please go through name manager to see various named formula define in there.

Regards,