Pivot Table

Hi,

Attached is the test file I am currently working on.

For each dates, 3- 5 staffs were involved for the case specific category. Need to create a report every month to send to payroll for each staff who involved with the category type and the dates they were involved. Want clarification, is this the right way to record the data to extract desired out come?

Report 3 - I am trying to get dates for individual staff for the tasks they involved. Tried using with pivot table and couldn't able to get the list of dates for each staff.( report needed for each month)

Any suggestions and ideas, greatly appreciated.

Thanks.test1x.jpg

To be honest, I would set up the table in a completely different format: Use the date as a report filter. Then you would be able to only display the relevant time frame (each month so to speak). Then, the row label would be Person-ID. The coloumn label would be the task. Use it also as Value - Count of. This way you can see for each person how many times in the selected time frame it was involved in which task.

Many thanks Wustuv for your reply.

I set it this way because I have linked this table to another workbook to create a time sheet invoice for each row (using index/match function). Each row is one surgery (case), and Person 1,2, are the staffs involved in the particular case. Is there any way, I can create an another worksheet in the way you have suggested using the primary data.

Hi stasinek,

Many thanks for the great work and posting me the attachment. I will be grateful, if you could explain me the steps involved to convert the layout from the primary data ( the method you've used). Can it be done, by linking the new layout to the primary data so this will be updated automatically when data added to the primary data?

Hurrah ! -I've managed to get the dates for the individual staff with your Pivot Table.
Tx1.jpg
tx2.jpg

To automate the conversion table to the new layout, use the add-Power Query

Many Thanks, stasinek. I am still learning excel. I will look about power query and explore it.

Any good websites to learn about power query?

Thanks in advance.

Originally Posted by mailidjoshua

Any good websites to learn about power query?

Thanks in advance.

Managed to find more about power query on the Excel Forum and able to sort the data with power query.
http://www.excelforum.com/excel-gene...h-headers.html


I am grateful to all the contributors and many thanks to the Excel Forum.