Forum Discussion
Convert one table into several tables
Hi there,
I have a table with columns: employee ID's, hours amounts, and wage types. I created a pivot table to break wage types into their own columns listing hours and amounts.
But I need each wage type to be it's own table with employee I'd, amount and hours.
I can splice the table, but I want it broken out into several tables so that the data can be filtered and analyzed and others can leave notes.
I would like to keep this on one sheet so that the data can be analyzed by multiple specialists in Excel web apps on SharePoint.
I have about 12 wage types but upwards of 30,000 employees. Not all wage types have hours. But all wage types have amounts.
I am hoping that the solution can be recorded with a macro as analysing is done about 9 times a week to varying populations.
Any assistance you are able to provide is greatly appreciated. I look forward to your response.
Thank you,
Snickers_&_Smores
I have a table with columns: employee ID's, hours amounts, and wage types. I created a pivot table to break wage types into their own columns listing hours and amounts.
But I need each wage type to be it's own table with employee I'd, amount and hours.
I can splice the table, but I want it broken out into several tables so that the data can be filtered and analyzed and others can leave notes.
I would like to keep this on one sheet so that the data can be analyzed by multiple specialists in Excel web apps on SharePoint.
I have about 12 wage types but upwards of 30,000 employees. Not all wage types have hours. But all wage types have amounts.
I am hoping that the solution can be recorded with a macro as analysing is done about 9 times a week to varying populations.
Any assistance you are able to provide is greatly appreciated. I look forward to your response.
Thank you,
Snickers_&_Smores
Hi
I'm not sure. Are you looking for this:
Learn Excel - Replicate Pivot Table For Each Rep - Podcast 2005
- Thank you so much Detlef. I did see this. But the tables are on separate worksheets, and I was hoping to have them on the same worksheet due to having specialists analysing data in webaps. That being said; I now realize that I may be able to make this work, as I can record a macro to have all worksheets combined into one after the specialists filtered each table for the highs and lows etc. Bu,t I wonder if I can now filter each table independent of the pivot table. This may post a challenge. I will post in.few days.
- if all your data is in a single structured table you can use "Match", "countif" and "Offset" to divide your table in to multiple tables according to the criteria you wish.
however if you have seperate tables you need to combine them in to one.
