Compile data

[ghederberg]

Hi,

Im having a problem where I want to compile data from several rows into 1 single row.
The data in this case is time and attendance data to upload into a payroll software.
There are 9 different columns as follows:

• Code
• Code nr
• Code name
• Employee ID
• Employee name
• Number (hours/days)
• Type of number (hours/days)
• Start Date
• End date

If we as an example say that I register 5 days holiday in the system it will look like this in the excel I receive:

Code	Code nr	Code Name	Employee ID	EEname	Number(hours/days)	Type of nr (hours/days)	Start Date	End date
T	111	Vacation	12345	Donald Duck	1	Days	24/07/2017	24/07/2017
T	111	Vacation	12345	Donald Duck	1	Days	24/07/2017	25/07/2017
T	111	Vacation	12345	Donald Duck	1	Days	26/07/2017	26/07/2017
T	111	Vacation	12345	Donald Duck	1	Days	27/07/2017	27/07/2017
T	111	Vacation	12345	Donald Duck	1	Days	28/07/2017	28/07/2017

<tbody>
</tbody>

I want to create something within XL that can do like this

Code	Code nr	Code name	Employee ID	EE name	Number	Type of nr (hours/days)	Start Date	End date
T	111	Vacation	12345	Donald Duck	5	Days	24/07/2017	28/07/2017

<tbody>
</tbody>

Above example is of 1 employee and 1 absence type. In the actual report there are around 650 employees with 5-6 different absence/attendance types. Further if the period of leave is not 1 full period I want to split those rows up.
Example lets say I took vacation 24th july, came back to work on the 25th and then took 3 days between 26-28 it would look like this in the excel.

Code	Code nr	Code name	Employee number	Employee name	number	type of number (hours/days)	Start Date	End date
T	111	Vacation	12345	Donald Duck	1	Days	24/07/2017	24/07/2017
T	111	Vacation	12345	Donald Duck	1	Days	26/07/2017	26/07/2017
T	111	Vacation	12345	Donald Duck	1	Days	27/07/2017	27/07/2017
T	111	Vacation	12345	Donald Duck	1	Days	28/07/2017	28/07/2017

<tbody>
</tbody>

I want to create something in excel making it look like this

Code	Code nr	Code name	Employee nr	Employee name	number	type of nr (hours/days)	Start Date	End date
T	111	Vacation	12345	Donald Duck	1	Days	24/07/2017	24/07/2017
T	111	Vacation	12345	Donald Duck	3	Days	26/07/2017	28/07/2017

<tbody>
</tbody>

Again, above is only 1 example. Lets say I have 1 day vacation and then 2 days sickleve. I dont want those 2 absence types to be together.
In the excel I get from the software it would look like this:

Code	Code nr	Code name	Employee nr	Employee name	number	type of nr (days/hours)	Start date	End date
T	111	Vacation	12345	Donald duck	1	Days	24/07/2017	24/07/2017
T	112	Sickness	12345	Donald duck	8	hours	25/07/2017	25/07/2017
T	112	Sickness	12345	Donald Duck	8	hours	26/07/2017	26/07/2017

<tbody>
</tbody>

I want to create something in excel making it look like this:

Code	Code nr	Code name	Employee nr	Employee name	number	type of nr (days/hours)	Start date	End date
T	111	Vacation	12345	Donald duck	1	1	24/07/2017	24/07/2017
T	112	Sickness	12345	Donald Duck	16	hours	25/07/2017	26/07/2017

<tbody>
</tbody>

Any assistance would be really appreciated

Thanks

 

[Special-K99]

Try Googling Pivot Table

 

[ghederberg]

Thanks,
been trying pivot, it only solves a part of the problem. Adding the dates "from/to" is where the pivot fails. It does not compile 3 rows into 1, it will give 1 row per employee and date. In case you got any great tips on how that can be solved within pivot youre a star

 

[Special-K99]

Unfortunately I havent had much experience with pivot tables.