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?
 

Some videos you may like

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

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.
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,105,977
Messages
5,508,527
Members
408,688
Latest member
Bhojraj

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top