Trying to move horizontal data into vertical data with other fields

[richard12]

I have lists of vacation times in a spreadsheet with multiple vacation columns that need to be placed, with other data, into a vertical data formatted spreadsheet. This now requires multiple cut/paste into another sheet, then upload to Microsoft Access to create relationships in a query that is copy/pasted into a spreadsheet to get the multiple rows of the same employee with different vacation dates. I would like to eliminate using Access, if possible, and use Excel to get to the sheet that can be used in a program. There is an employee sheet that has rows of employees with their personal data that is used to make a final Excel worksheet. The goal is to make a sheet with the same employee multiple times so vacation weeks can be accounted for and used in a query. See Sheet 2 example below. I am using Excel 2013

Sheet1 from managers
Grp Emp# LastNM FirstNM Vac Week Vac Week Vac Week Vac Week Vac Week Vac

[TABLE="width: 991"]
<colgroup><col><col><col><col><col><col span="5"></colgroup><tbody>[TR]
[TD]02[/TD]
[TD]503941[/TD]
[TD]SMITH[/TD]
[TD]JOHN[/TD]
[TD="align: right"]3/11/2018[/TD]
[TD="align: right"]6/17/2018[/TD]
[TD="align: right"]9/30/2018[/TD]
[TD="align: right"]11/4/2018[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]02[/TD]
[TD]500007[/TD]
[TD]JONES[/TD]
[TD]ANDY[/TD]
[TD="align: right"]1/28/2018[/TD]
[TD="align: right"]2/4/2018[/TD]
[TD="align: right"]4/22/2018[/TD]
[TD="align: right"]7/1/2018[/TD]
[TD="align: right"]11/18/2018[/TD]
[TD="align: right"]11/25/2018[/TD]
[/TR]
[TR]
[TD]02[/TD]
[TD]544899[/TD]
[TD]ANDERSON[/TD]
[TD]MAX[/TD]
[TD="align: right"]4/29/2018[/TD]
[TD="align: right"]5/6/2018[/TD]
[TD="align: right"]5/13/2018[/TD]
[TD="align: right"]12/30/2018[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]03[/TD]
[TD]544671[/TD]
[TD]MAUL[/TD]
[TD]MIKE[/TD]
[TD="align: right"]3/25/2018[/TD]
[TD="align: right"]4/1/2018[/TD]
[TD="align: right"]7/8/2018[/TD]
[TD="align: right"][/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]03[/TD]
[TD]543445[/TD]
[TD]JONES[/TD]
[TD]SAM[/TD]
[TD="align: right"]5/6/2018[/TD]
[TD="align: right"]8/5/2018[/TD]
[TD="align: right"]8/12/2018[/TD]
[TD="align: right"]10/7/2018[/TD]
[TD="align: right"]11/11/2018[/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]

Sheet2 is using manager input and HR employee data from separate pages. I need to somehow have multiple rows of the same data to meet the number of columns of vacation week dates. The purpose is to have this data so the selection of an employee's vacation week might find similar employees, not on vacation, who could substitute for the employee on vacation. The order or format of Sheet 2 is not important if that matters.
[TABLE="width: 456"]
<colgroup><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]EMPLOYEE#[/TD]
[TD]LastNM[/TD]
[TD]FirstNM[/TD]
[TD]ZIP[/TD]
[TD]EstZIP[/TD]
[TD]VacWeek[/TD]
[/TR]
[TR]
[TD]503941[/TD]
[TD]SMITH[/TD]
[TD]JOHN[/TD]
[TD]54126[/TD]
[TD]54344[/TD]
[TD="align: right"]3/11/2018[/TD]
[/TR]
[TR]
[TD]503941[/TD]
[TD]SMITH[/TD]
[TD]JOHN[/TD]
[TD]54126[/TD]
[TD]54344[/TD]
[TD="align: right"]6/17/2018[/TD]
[/TR]
[TR]
[TD]503941[/TD]
[TD]SMITH[/TD]
[TD]JOHN[/TD]
[TD]54126[/TD]
[TD]54344[/TD]
[TD="align: right"]9/30/2018[/TD]
[/TR]
[TR]
[TD]503941[/TD]
[TD]SMITH[/TD]
[TD]JOHN[/TD]
[TD]54126[/TD]
[TD]54344[/TD]
[TD="align: right"]11/4/2018[/TD]
[/TR]
[TR]
[TD]500007[/TD]
[TD]JONES[/TD]
[TD]ANDY[/TD]
[TD]52537[/TD]
[TD]54443[/TD]
[TD="align: right"]1/28/2018[/TD]
[/TR]
[TR]
[TD]500007[/TD]
[TD]JONES[/TD]
[TD]ANDY[/TD]
[TD]52537[/TD]
[TD]54443[/TD]
[TD="align: right"]2/4/2018[/TD]
[/TR]
[TR]
[TD]500007[/TD]
[TD]JONES[/TD]
[TD]ANDY[/TD]
[TD]52537[/TD]
[TD]54443[/TD]
[TD="align: right"]4/22/2018[/TD]
[/TR]
[TR]
[TD]500007[/TD]
[TD]JONES[/TD]
[TD]ANDY[/TD]
[TD]52537[/TD]
[TD]54443[/TD]
[TD="align: right"]7/1/2018[/TD]
[/TR]
[TR]
[TD]500007[/TD]
[TD]JONES[/TD]
[TD]ANDY[/TD]
[TD]52537[/TD]
[TD]54443[/TD]
[TD="align: right"]11/18/2018[/TD]
[/TR]
[TR]
[TD]500007[/TD]
[TD]JONES[/TD]
[TD]ANDY[/TD]
[TD]52537[/TD]
[TD]54443[/TD]
[TD="align: right"]11/25/2018[/TD]
[/TR]
</tbody>[/TABLE]

 

[Fluff]

Glad to help & thanks for the feedback