Reproducing a data table differently

[Cyberbeing]

Hello

Can someone please help with this?

I make a Manpower Plan like this.

MANPOWER PLAN
NAME	1-Apr	2-Apr	3-Apr	4-Apr	5-Apr	6-Apr	7-Apr
NAME1	AM	PM	NIGHT	OFF	AM	PM	NIGHT
NAME2	OFF	AM	PM	NIGHT	OFF	AM	PM
NAME3	NIGHT	OFF	AM	PM	NIGHT	OFF	AM
NAME4	PM	NIGHT	OFF	AM	PM	NIGHT	OFF
NAME5	AM	PM	NIGHT	OFF	AM	PM	NIGHT
NAME6	OFF	AM	PM	NIGHT	OFF	AM	PM
NAME7	NIGHT	OFF	AM	PM	NIGHT	OFF	AM
NAME8	PM	NIGHT	OFF	AM	PM	NIGHT	OFF

<tbody>
</tbody>

I like to convert it automatically to this.

SCHEDULE
1-Apr	2-Apr	3-Apr	4-Apr	5-Apr	6-Apr	7-Apr
AM
NAME1	NAME2	NAME3	NAME4	NAME1	NAME2	NAME3
NAME5	NAME6	NAME7	NAME8	NAME5	NAME6	NAME7
PM
NAME4	NAME1	NAME2	NAME3	NAME4	NAME1	NAME2
NAME8	NAME5	NAME6	NAME7	NAME8	NAME5	NAME6
NIGHT
NAME3	NAME4	NAME1	NAME2	NAME3	NAME4	NAME1
NAME7	NAME8	NAME5	NAME6	NAME7	NAME8	NAME5
OFF
NAME2	NAME3	NAME4	NAME1	NAME2	NAME3	NAME4
NAME6	NAME7	NAME8	NAME5	NAME6	NAME7	NAME8

<tbody>
</tbody>

Could you tell me how to do it.
I am thankful for all help.
Regards

 

[Andrew Poulsom]

Use the macro here to convert your data to first normal form:

http://spreadsheetpage.com/downloads/xl/makedatabasetable.txt

Then you can use Excel's built-in data analysis features, like AutoFilter and Pivot Tables.

 

[Cyberbeing]

Use the macro here to convert your data to first normal form:

http://spreadsheetpage.com/downloads/xl/makedatabasetable.txt

Then you can use Excel's built-in data analysis features, like AutoFilter and Pivot Tables.

Thank you Andrew for replying. The filter function is not ideal in my case because the 2nd table need to help create the daily deployment of about 50 crew members interchangeable serving about 5 posts. So the person doing should only see the members scheduled for that shift. Here is the sample of the table. With many thanks.

DAILY DEPLOYMENT
1-Apr	2-Apr	3-Apr
Post 1	Post 2	Post 1	Post 2	Post 1	Post 2
AM	AM	AM
NAME1	NAME5	NAME2	NAME6	NAME3	NAME7
PM	PM	PM
NAME4	NAME8	NAME1	NAME5	NAME2	NAME6
NIGHT	NIGHT	NIGHT
NAME3	NAME7	NAME4	NAME8	NAME1	NAME5

<colgroup><col span="6"></colgroup><tbody>
</tbody>

 

[Andrew Poulsom]

Can't you sort your data to get the output you want? here's what I created by filtering out OFF and using a Custom List to sort:

Excel 2010
	J	K	L
1	Name	Date	Shift
2	NAME1	01-Apr	AM
3	NAME5	01-Apr	AM
4	NAME4	01-Apr	PM
5	NAME8	01-Apr	PM
6	NAME3	01-Apr	NIGHT
7	NAME7	01-Apr	NIGHT
10	NAME2	02-Apr	AM
11	NAME6	02-Apr	AM
12	NAME1	02-Apr	PM
13	NAME5	02-Apr	PM
14	NAME4	02-Apr	NIGHT
15	NAME8	02-Apr	NIGHT
Sheet1

 

[Cyberbeing]

Thanks, I am going to try your method on the full list for about 50 members over a full month. Could you tell me how you work the Customs Lists. Regards

 

[Andrew Poulsom]

What version of Excel do you have?

 

[Cyberbeing]

Sorry for late reply Andrew, was night this side of the world. I use Excel 2010.

If I may clarify, the worksheets are to ensure that the Supervisor doing the Daily Deployment does not

[1] Enter a name that is not on the list or

[2] repeat the same name for 2 shifts or different locations
[e.g. Joe should not appear on AM and PM shift for the same day, and/or for Site 1 and Site 2]

[3] enter a name that is not scheduled for the shift.

I have solved for [1] by using Data Validation and [2] by using Conditional Formatting.
I need to solve for [3] by for example allowing person the Supervisor see only names of crew that are scheduled for the date and the shift.

We are working with about 50 crew members per shift and serve 5 sites so that is about 150 names per day.
Also the Manpower Plan [we call it the Monthly Roster] is updated 4 weeks in advance so crew members know their work schedule well in advance.

I am grateful for your involvement. I will be away from the computer for about +12 hours so apologize in advance for late response.
With Regards

 

[Andrew Poulsom]

Edit Custom Lists is in the General Category on the Advanced tab of the Excel Options dialog. More here:

How To Create A Custom List In Excel 2010

 

[Cyberbeing]

Thank you sir for the help. Much appreciated.