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:

[TABLE="class: grid, width: 800"]
<tbody>[TR]
[TD]Code[/TD]
[TD]Code nr[/TD]
[TD]Code Name[/TD]
[TD]Employee ID[/TD]
[TD]EEname[/TD]
[TD]Number(hours/days)[/TD]
[TD]Type of nr (hours/days)[/TD]
[TD]Start Date[/TD]
[TD]End date[/TD]
[/TR]
[TR]
[TD]T[/TD]
[TD]111[/TD]
[TD]Vacation[/TD]
[TD]12345[/TD]
[TD]Donald Duck[/TD]
[TD]1[/TD]
[TD]Days[/TD]
[TD]24/07/2017[/TD]
[TD]24/07/2017[/TD]
[/TR]
[TR]
[TD]T[/TD]
[TD]111[/TD]
[TD]Vacation[/TD]
[TD]12345[/TD]
[TD]Donald Duck[/TD]
[TD]1[/TD]
[TD]Days[/TD]
[TD]24/07/2017[/TD]
[TD]25/07/2017[/TD]
[/TR]
[TR]
[TD]T[/TD]
[TD]111[/TD]
[TD]Vacation[/TD]
[TD]12345[/TD]
[TD]Donald Duck[/TD]
[TD]1[/TD]
[TD]Days[/TD]
[TD]26/07/2017[/TD]
[TD]26/07/2017[/TD]
[/TR]
[TR]
[TD]T[/TD]
[TD]111[/TD]
[TD]Vacation[/TD]
[TD]12345[/TD]
[TD]Donald Duck[/TD]
[TD]1[/TD]
[TD]Days[/TD]
[TD]27/07/2017[/TD]
[TD]27/07/2017[/TD]
[/TR]
[TR]
[TD]T[/TD]
[TD]111[/TD]
[TD]Vacation[/TD]
[TD]12345[/TD]
[TD]Donald Duck[/TD]
[TD]1[/TD]
[TD]Days[/TD]
[TD]28/07/2017[/TD]
[TD]28/07/2017[/TD]
[/TR]
</tbody>[/TABLE]

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

[TABLE="class: grid, width: 800"]
<tbody>[TR]
[TD]Code[/TD]
[TD]Code nr[/TD]
[TD]Code name[/TD]
[TD]Employee ID[/TD]
[TD]EE name[/TD]
[TD]Number[/TD]
[TD]Type of nr (hours/days)[/TD]
[TD]Start Date[/TD]
[TD]End date[/TD]
[/TR]
[TR]
[TD]T[/TD]
[TD]111[/TD]
[TD]Vacation[/TD]
[TD]12345[/TD]
[TD]Donald Duck[/TD]
[TD]5[/TD]
[TD]Days[/TD]
[TD]24/07/2017[/TD]
[TD]28/07/2017[/TD]
[/TR]
</tbody>[/TABLE]


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.
[TABLE="class: grid, width: 800"]
<tbody>[TR]
[TD]Code[/TD]
[TD]Code nr[/TD]
[TD]Code name[/TD]
[TD]Employee number[/TD]
[TD]Employee name[/TD]
[TD]number[/TD]
[TD]type of number (hours/days)[/TD]
[TD]Start Date[/TD]
[TD]End date[/TD]
[/TR]
[TR]
[TD]T[/TD]
[TD]111[/TD]
[TD]Vacation[/TD]
[TD]12345[/TD]
[TD]Donald Duck[/TD]
[TD]1[/TD]
[TD]Days[/TD]
[TD]24/07/2017[/TD]
[TD]24/07/2017[/TD]
[/TR]
[TR]
[TD]T[/TD]
[TD]111[/TD]
[TD]Vacation[/TD]
[TD]12345[/TD]
[TD]Donald Duck[/TD]
[TD]1[/TD]
[TD]Days[/TD]
[TD]26/07/2017[/TD]
[TD]26/07/2017[/TD]
[/TR]
[TR]
[TD]T[/TD]
[TD]111[/TD]
[TD]Vacation[/TD]
[TD]12345[/TD]
[TD]Donald Duck[/TD]
[TD]1[/TD]
[TD]Days[/TD]
[TD]27/07/2017[/TD]
[TD]27/07/2017[/TD]
[/TR]
[TR]
[TD]T[/TD]
[TD]111[/TD]
[TD]Vacation[/TD]
[TD]12345[/TD]
[TD]Donald Duck[/TD]
[TD]1[/TD]
[TD]Days[/TD]
[TD]28/07/2017[/TD]
[TD]28/07/2017[/TD]
[/TR]
</tbody>[/TABLE]

I want to create something in excel making it look like this
[TABLE="class: grid, width: 800"]
<tbody>[TR]
[TD]Code[/TD]
[TD]Code nr[/TD]
[TD]Code name[/TD]
[TD]Employee nr[/TD]
[TD]Employee name[/TD]
[TD]number[/TD]
[TD]type of nr (hours/days)[/TD]
[TD]Start Date[/TD]
[TD]End date[/TD]
[/TR]
[TR]
[TD]T[/TD]
[TD]111[/TD]
[TD]Vacation[/TD]
[TD]12345[/TD]
[TD]Donald Duck[/TD]
[TD]1[/TD]
[TD]Days[/TD]
[TD]24/07/2017[/TD]
[TD]24/07/2017[/TD]
[/TR]
[TR]
[TD]T[/TD]
[TD]111[/TD]
[TD]Vacation[/TD]
[TD]12345[/TD]
[TD]Donald Duck[/TD]
[TD]3[/TD]
[TD]Days[/TD]
[TD]26/07/2017[/TD]
[TD]28/07/2017[/TD]
[/TR]
</tbody>[/TABLE]

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:
[TABLE="class: grid, width: 800"]
<tbody>[TR]
[TD]Code[/TD]
[TD]Code nr[/TD]
[TD]Code name[/TD]
[TD]Employee nr[/TD]
[TD]Employee name[/TD]
[TD]number[/TD]
[TD]type of nr (days/hours)[/TD]
[TD]Start date[/TD]
[TD]End date
[/TD]
[/TR]
[TR]
[TD]T[/TD]
[TD]111[/TD]
[TD]Vacation[/TD]
[TD]12345[/TD]
[TD]Donald duck[/TD]
[TD]1[/TD]
[TD]Days[/TD]
[TD]24/07/2017[/TD]
[TD]24/07/2017[/TD]
[/TR]
[TR]
[TD]T[/TD]
[TD]112[/TD]
[TD]Sickness[/TD]
[TD]12345[/TD]
[TD]Donald duck[/TD]
[TD]8[/TD]
[TD]hours[/TD]
[TD]25/07/2017[/TD]
[TD]25/07/2017[/TD]
[/TR]
[TR]
[TD]T[/TD]
[TD]112[/TD]
[TD]Sickness[/TD]
[TD]12345[/TD]
[TD]Donald Duck[/TD]
[TD]8[/TD]
[TD]hours[/TD]
[TD]26/07/2017[/TD]
[TD]26/07/2017[/TD]
[/TR]
</tbody>[/TABLE]

I want to create something in excel making it look like this:
[TABLE="class: grid, width: 800"]
<tbody>[TR]
[TD]Code[/TD]
[TD]Code nr[/TD]
[TD]Code name[/TD]
[TD]Employee nr[/TD]
[TD]Employee name[/TD]
[TD]number[/TD]
[TD]type of nr (days/hours)[/TD]
[TD]Start date[/TD]
[TD]End date[/TD]
[/TR]
[TR]
[TD]T[/TD]
[TD]111[/TD]
[TD]Vacation[/TD]
[TD]12345[/TD]
[TD]Donald duck[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]24/07/2017[/TD]
[TD]24/07/2017[/TD]
[/TR]
[TR]
[TD]T[/TD]
[TD]112[/TD]
[TD]Sickness[/TD]
[TD]12345[/TD]
[TD]Donald Duck[/TD]
[TD]16[/TD]
[TD]hours[/TD]
[TD]25/07/2017[/TD]
[TD]26/07/2017[/TD]
[/TR]
</tbody>[/TABLE]

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.