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

02	503941	SMITH	JOHN	3/11/2018	6/17/2018	9/30/2018	11/4/2018
02	500007	JONES	ANDY	1/28/2018	2/4/2018	4/22/2018	7/1/2018	11/18/2018	11/25/2018
02	544899	ANDERSON	MAX	4/29/2018	5/6/2018	5/13/2018	12/30/2018
03	544671	MAUL	MIKE	3/25/2018	4/1/2018	7/8/2018
03	543445	JONES	SAM	5/6/2018	8/5/2018	8/12/2018	10/7/2018	11/11/2018

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

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.

EMPLOYEE#	LastNM	FirstNM	ZIP	EstZIP	VacWeek
503941	SMITH	JOHN	54126	54344	3/11/2018
503941	SMITH	JOHN	54126	54344	6/17/2018
503941	SMITH	JOHN	54126	54344	9/30/2018
503941	SMITH	JOHN	54126	54344	11/4/2018
500007	JONES	ANDY	52537	54443	1/28/2018
500007	JONES	ANDY	52537	54443	2/4/2018
500007	JONES	ANDY	52537	54443	4/22/2018
500007	JONES	ANDY	52537	54443	7/1/2018
500007	JONES	ANDY	52537	54443	11/18/2018
500007	JONES	ANDY	52537	54443	11/25/2018

<colgroup><col><col><col><col><col><col></colgroup><tbody>
</tbody>

 

[Fluff]

Glad to help & thanks for the feedback