Combining two dissimilar sheets into one sheet for pivot table analysis

[ribberflavenous]

I have been scratching my head for some time on how to deal with this, I hope someone can help. I am using Excel 2010

I have 2 different systems, Quote and Workorder and I am trying to pull the data together for a combined report that will allow me to see, on a single line, the total data from quotes and work orders, by customer and time period. Data is provided weekly to be appended to the existing history table, so the structure/order of each sheet must be maintained but I can change the heading/label of the column so that they match pertinent data in each sheet (such as Customer, Date, Type, Total $...etc).

I am trying to create a single sheet (without having to cut/paste) that will have all Quotes and all Workorder detail, but only the fields that I need which match. The number of records will continuously change on both sheets. Once I have it in this format, I can use a pivot table to get what I need in the report.

 

[theBardd]

If there is a column on each sheet that links the two together, some ID column, you can join the two tables in PowerPivot and then pivot them both to get your results.

 

[miguel.escobar]

I'll leave you with some links that will help you.
Combine Multiple Worksheets/Workbooks into a Single PowerPivot Table « PowerPivotPro
http://www.contextures.com/PowerPivot-Identical-Excel-Files.html

you could use Power Query and then a Linked Table but I'm not sure you might want that.