merge data from 2 worksheets - not identical data layout

[GorD]

Using Business Warehouse, i have two sources of timesheet bookings and i have worked at getting these onto 2 woksheets in the same workbook (possibly a mistake) - one directly from a shop floor data package and the other for manual bookings. Together these make up the total hrs booked to a contact.

Many of the fields are the same but many aren't
Is there an easy way i,m missing to create another table merging the two data sets


Also the shop floor system lists a product type and a shop order - i use the product no to reference another table giving the Contract

But the non shop floor are booked to a Project activity, which references a separate table to get the contract.d

At present i have two standard pivot tables and then a summary table ( using sumifs/sumproducts) to get the total hrs booked per week.

Hope this makes some sense

 

[josephnorat]

This looks like it might be a better job for an ETL tool. Have you worked with Pentaho Kettle before?

Merging data can be done very simply with Kettle. It probably won't be as quick a solution to begin with as you'll have to learn the new software, but in terms of a long term solution it would be very effective.
http://kettle.pentaho.com/

 

[miguel.escobar]

If you're not planning on using this on Sharepoint then you might consider using Power Query formerly known as Data Explorer. You'd do the transformation and merge of those tables with Power Query and then load the merged/appended table into