Converting data between tabs

It's been awhile, but hopefully someone can help here. I am attempting to build a new performance tracking tool. Just to give a little insight; each employee has 3 functions - RCV, FH, and RCH. Each week, I need to see the qty and time spent for each function and then determine their performance. I pull this information from our operation system and use a macro to clean it up. I can then paste the values into the tab named "RAW." This tab contains a list of employees, the data, and the dates. The tab named "332082" is the individual employees info (each would have their own). This tab uses the values from the "RAW" each week and calculates their performance and displays it in the PPM column. This tab also keeps track of their overall average and cumulative qty/time. The "TEAM" tab uses the data from the individual employee tabs to keep track of the overall teams performance each week. The individual employee and team tabs provide an much more useful layout, but the RAW tab is the only efficient way I have to get the info into the file.

Please see attached file.

The first thing that I am trying to work out is the best way to convert the data as displayed on the "RAW" tab to the format shown on the "332082" tab. If anyone has any ideas to best accomplish this please let me know.

It took me a while to get some kind of idea what you're wanting (at least I think I was on the right track), but it made NO sense to me at first because your numbers weren't matching up correctly. For example: On Raw Data tab, Dave M. has a RCV QTY of 220 but on his tab (149798) the number is 367. The 220 is on Jesse's tab. Please address these discrepancies and upload a file with all data in its proper place. After everything is cleaned up, I don't think this will be difficult to solve.

File attched

Anyone have any suggestions for this? I think I could add a ridiculous amount of vlookups, but looking for something a little cleaner/more efficient. Basically need to take information from one tab that is in a horizontal format and distribute it to several individual tabs in a vertical format. Hopefully once you see my first post and look at the file it will make sense.

Could be an option. I was already planning to set up some pivot tables to create a dash board on the first tab and then hide all the others. I have been trying to find the best way to set all this up for awhile, so I am open to any suggestions you may have.

I should mention though - the "Raw Data" tab is set up to match the source of the information from our computer system. The layout on this tab cannot be changed. As you see, it is not visually useful. The other tabs are an attempt to make the data more user friendly and make the calculations. These could be changed if needed.

Wow that was quick. The level of talent on this forum never fails to amaze me.

The macro works great and may solve some future problems that I expect to encounter when creating the dash board. However, I still have some issues.

First, I just want to expand a little on what I am trying to accomplish to give everyone a clearer understanding. Each week I will need to collect six sets of data (RCV Qty, RCV Time, FH Qty, FH Time, RCH Qty, RCH Time) for each employee and dump it into the "Raw Data" tab. This is the critical data needed to calculate performance over time and record cumulative quantiles and time spent for each. Other than dumping in new info each week, this tab is not intended to be used as it would be very cumbersome to look through.

Second, once the raw data is in it needs to be converted to a user friendly format that calculates performance for each of the 3 tasks, for each employee, by date. Currently, this is shown in the individual employee tabs. Each employee tab uses the data from "RAW" to calculate performance (RCV PPM, FH PPM, RCH PPM) and keeps track of cumulative quantities and time. This tab will provide a clean, user friendly, performance record that lower level management can use to monitor and coach their employees. The individual tabs should automatically update when the "Raw" tab is completed each week. This would allow lower management to just view and print their employees record. The "Team" tab is intended to be the same concept, just for the entire department.

Third, I want to set up a dash board using all this info. With the use of pivot tables, slicers, and charts senior management can look for problem areas, and trends over time using multiple variables.

The issue that I am seeing with your recommendation is that it while it does a great job at sorting the raw data, it ignores the output data needed (RCV PPM, FH PPM, RCV PPM, Avg PPM). I could combine the information into one tab, but then would lose the clean user friendly format for lower lever management to use with individual employees. If combined, filters and sorting could be used to clean it up, but the more cumbersome it is to use, the less likely it is to be used properly. The format really needs to be so they can just open/print/go and have the information neatly arranged without extra work. If able to make work, the layout on the current employee tabs is what is easiest for them to use.

Other than this, it seems that due to the reliance on a macro to sort, a new pivot table would need to be created every week when new weekly data is added. Without this macro, I thought I could set a defined range for the pivot table that will allow weekly growth and automatically update. ---- I may be wrong in this though.

Anyway, I greatly appreciate the help, and as always, all suggestions are highly valued.

Wow that was quick. The level of talent on this forum never fails to amaze me.

The macro works great and may solve some future problems that I expect to encounter when creating the dash board. However, I still have some issues.

First, I just want to expand a little on what I am trying to accomplish to give everyone a clearer understanding. Each week I will need to collect six sets of data (RCV Qty, RCV Time, FH Qty, FH Time, RCH Qty, RCH Time) for each employee and dump it into the "Raw Data" tab. This is the critical data needed to calculate performance over time and record cumulative quantiles and time spent for each. Other than dumping in new info each week, this tab is not intended to be used as it would be very cumbersome to look through.

Second, once the raw data is in it needs to be converted to a user friendly format that calculates performance for each of the 3 tasks, for each employee, by date. Currently, this is shown in the individual employee tabs. Each employee tab uses the data from "RAW" to calculate performance (RCV PPM, FH PPM, RCH PPM) and keeps track of cumulative quantities and time. This tab will provide a clean, user friendly, performance record that lower level management can use to monitor and coach their employees. The individual tabs should automatically update when the "Raw" tab is completed each week. This would allow lower management to just view and print their employees record. The "Team" tab is intended to be the same concept, just for the entire department.

Third, I want to set up a dash board using all this info. With the use of pivot tables, slicers, and charts senior management can look for problem areas, and trends over time using multiple variables.

The issue that I am seeing with your recommendation is that it while it does a great job at sorting the raw data, it ignores the output data needed (RCV PPM, FH PPM, RCV PPM, Avg PPM). I could combine the information into one tab, but then would lose the clean user friendly format for lower lever management to use with individual employees. If combined, filters and sorting could be used to clean it up, but the more cumbersome it is to use, the less likely it is to be used properly. The format really needs to be so they can just open/print/go and have the information neatly arranged without extra work. If able to make work, the layout on the current employee tabs is what is easiest for them to use.

Other than this, it seems that due to the reliance on a macro to sort, a new pivot table would need to be created every week when new weekly data is added. Without this macro, I thought I could set a defined range for the pivot table that will allow weekly growth and automatically update. ---- I may be wrong in this though.

Anyway, I greatly appreciate the help, and as always, all suggestions are highly valued.

The columns are XXX PPH, not XXX PPM - sorry about the typo. They are located in the employee tabs and team tab named "Team", "149798", "334037", "332082." - these columns are not in the raw data table, but they use the info from the raw to make the calculations.

Note: The "149798" "334037" 332082" are employees - once I get a working concept, this would expand to about 40 employees.

oeldere - after some restructuring, I think I may be able to get your original suggestion to work out. I will take a few more days to play with it and let you know for sure.

--Thanks again