How would you merge disparate datasets?

[fiats3]

I have an Excel workbook with approximately 35 tabs of ticketing data from other Native Ticketing Systems (Remedy, Mange Now, etc.). Each tab contains a differing number of columns (range could be 10 to 100+) with different header field names ('Resolve' vs.'Resolved Date', Assigned vs. 'Assignee Name', etc.). I need to 'map' the closing matching header field names in this process, by consolidate all 'Assigned' with all 'Assignee Name', etc. There are many instances like that between the 35 tabs. In cases, where there is no field name equivalency and I will have to use a proxy field, or leave null. The number of records in each tab ranges from 0 - 7000.

What I want to do is consolidate the data from all the tabs in the workbook and create just a single tab of merged records that use a universal syntax for each field name. It can be created in a new workbook, or as a new tab in the original workbook.

Anyone have any tricks/ideas that don't involve a VBA script?

 

[Fazza]

Without VBA, a query table.

Both the manual (non VBA code) and VBA approach are described in http://www.mrexcel.com/forum/showthread.php?t=315768 for a pivot table (that is, not a query table). A query table is virtually identical except start with external data query [ALT-D-D-N] not pivot table [ALT-D-P].

Can be either a new workbook or tab in existing workbook. (If using a pivot table, start in a new workbook.) If you don't have defined names for the data, early on in the wizard you'll get a message about no visible tables, OK to acknoweldge that and then in options select 'system tables'. Then worksheet names should show. The above linked thread should explain all you need.