Self-Populating Data Table

[Ladyjet86]

Ok so I have made a self populating Data Table to show me which employee is on what shift that updates daily from our roster.

This works and it's brilliant, however I want to steam line my finished product further, but haven't been able to find an easy solution.

I have several employees per position and my data table currently self populates so that every employee has their own line. I want to populate it so that all employees with the same position all apear in 1 row across the columns under their current shift.

So this is what my end result currently looks like

Table 1

POSITION	SHIFT 1	SHIFT 2	SHIFT 3
POSITION 1	EMPLOYEE 1
POSITION 1		EMPLOYEE 5
POSITION 1			EMPLOYEE 8
POSITON 2	EMPLOYEE 4
POSITION 2		EMPLOYEE 12
POSITION 2			EMPLOYEE 20

<tbody>
</tbody>

This is how I want it to look.

Table 2

POSITION	SHIFT 1	SHIFT 2	SHIFT 3
POSITION 1	EMPLOYEE 1	EMPLOYEE 5	EMPLOYEE 8
POSITION 2	EMPLOYEE 4	EMPLOYEE 12	EMPLOYEE 20
POSITION 3	EMPLOYEE 56	EMPLOYEE	EMPLOYEE 9
POSITION 4	EMPLOYEE 10	EMPLOYEE 11	EMPLOYEE 12
POSITION 5	EMPLOYEE 13	EMPLOYEE 14	EMPLOYEE 15
POSITION 6	EMPLOYEE 16	EMPLOYEE 17	EMPLOYEE 18

<tbody>
</tbody>

We have upto 250 employees and upto 50 positions so you can understand why I need to stream line the table.

I can not change the roster as it is our orginal data as requires to be set up as it is. When adding a new employee into the roster I must enter them into there designated crew. "inserting a row midway down the original data".

If I use the CONCATENATE formula and add a new employee into the roster "half way down the original data" then it throws out what the CONCATENATE formula is looking up. It looks up the same cell but the names have all moved down by a cell therefore the populated information is wrong.

I am hoping to make my end result look like Table 2 even if it means adding another spread sheet in somewhere between the roster and the end product.

Realisticly I want a Pivot table that produces names not a tally of employees per shift.

Any and all help on this problem will be most appreciated.

 

[Andrew Poulsom]

How is your table 'self populating'?

 

[Ladyjet86]

It is quite complex and I don't know how well I can explain this.

So I have 1 tab that is using the reference formula to read from the roster. (another workbook)

Then I have a 2nd spreadsheet that uses a +HLOOKUP formulato read the data for each person for today's date.

Then we go to the final product that uses the data from spreadsheet 2 and put everyones name under the column that there shift is on.

I don't know how to up load screen shots of what I am trying to explain.

 

[Andrew Poulsom]

Why can't you 'self populate' the table in the format of Table 2 rather than Table 1?

 

[Ladyjet86]

Why can't you 'self populate' the table in the format of Table 2 rather than Table 1?

Because each person pulls to there own line

 

[Andrew Poulsom]

Are you using a macro to do that?