How would you merge disparate datasets?

fiats3

Board Regular
Joined
Nov 1, 2004
Messages
151
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?
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

Fazza

MrExcel MVP
Joined
May 17, 2006
Messages
9,368
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,133,274
Messages
5,657,782
Members
418,413
Latest member
Radoslaw Poprawski

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top