Excel-fi Question: Data, and not like the Android

• Trying to consolidate excel data with three variables (workbook date, name &widgets) and could use help. What is the most efficient way to combine this across weeks to make it summary with detail by week? What is the best tool to do this? Excel? Access? The fog of confusion follows clicking this link. I'm trying to make sense of widget numbers that have collected and stored by a different individual. They do not think the same way that I do, and I'm having trouble translating their data into a matrix that I can use for basic metrics. I have 26 workbooks (one for each week in the first half of this year). Each is dated for the week it was created. It contains a list of names of team members (between 20-40) and a list of widgets, but doesn't contain the date created. The widgets currently appear as row headings, and the columns contain the names of the individuals. The date isn't really captured in the body of the spreadsheet of the workbook. To make it a little harder to consolidate, there are random spaces in between to "make it pretty" which makes it very hard to consolidate. I know that I'll have to do a lot of manual work to consolidate it, but think it will be worth it if someone can help me understand how to work with data that has three variables. What is the most efficient way to combine this across weeks to make it summary with detail by week? Essentially I want to be able to see "Bobby" "Week Number" "Amount of Widgets" but am having trouble getting my brain to convert this data from two axis to three. Please let me know if I'm not including enough data to answer this question. I appear to have tons of it, and no way to use it.

• Answer:

  I decided to mock up some examples to show how easy it is to use a pivot table once you have the data in place. soelo has the correct idea: four columns for data, no blank lines. It would look http://i.imgur.com/N9YTTRc.jpg. Once you have that data in place make sure there's a header (title, whatever you want to call it) on every column. These are the keys that Excel will use to identify data when you're modifying the pivot table (everything in Row 01 in this example). Click anywhere within the data you wish to use, and then go to Insert -> Pivot Table. You'll get a blank table on the left with a floating box called 'Pivot Table Field List' on the right. From that field list you can choose which columns go into what areas. --- http://i.imgur.com/3JsfXWr.jpg. In A1,B1 you'll see a selection for Date. It's currently set to show (All) date ranges but you can drop that down to select any single date or any range of dates. Below that in the table is the list of worker names and underneath each name is the list of widgets they made in that date range selected. To the right is their total widget count, as well as count for individual widgets. If you look to the 'Pivot Table Field List' box on the right you'll see that 'Date' is in the Report Filter category, 'Worker Name' is in the Row Labels with 'Widget Name' nested underneath, and then the Values field is the 'Sum of Quantity'. All I did was drag those from the list of field at the top of the box down into those areas. --- http://i.imgur.com/dtAMlFw.jpg. I haven't changed any of the raw data, I've just moved the fields around in the 'Pivot Table Field List' box. Now the selector at the top is for the widgets themselves, and the rows show each worker name and the dates upon which they made the widgets in question, and how many. I could just as easily have nested the worker names underneath the dates if that were how we wanted to look at the data. --- http://i.imgur.com/9C3Lmzv.jpg but by no means anywhere near the limit of what a pivot table can do. In this example I've put both widget and date in the Report Filter area. This means you can select both a widget and a date and the pivot table will show who made them on that date and how many they made. I've selected a specific widget and date for this screenshot to make that more clear. --- Hopefully those screenshots will show how easy it is to take one nice clean spreadsheet full of data and use it to create a human-readable output in any form you desire. I did change the visual formatting of the pivot table a bit to make it easier to view in the screenshots, and if you start digging around in the settings you'll find there are many options for how data is presented.