Support DESK

Follow

Using matchIT On Demand to Transfer Data between 2 Files

  Previous Article Next Article

 

If you want to transfer data from one table to another where the records match, then you can do this using matchIT on Demand's overlap functionality. For example, suppose you wanted to merge records from Table A into Table B, copying data from one or more columns in Table A to Table B:

1.) Run an overlap job in matchIT On Demand with Table A set as the first table and Table B as the second by choosing 'Find matches that overlap two datasets (records from dataset 1 are removed from dataset 2 if they exist) ' from the task options in the Create a New Job page.

2.) Once matchIT on Demand has finished and you have downloaded the results, open Table B in Excel and add the Matching Groups output produced by matchIT On Demand into a second worksheet (Sheet2).  Make sure your Table B data is sorted by its Unique Ref column.

3.) In worksheet 2, remove any records that are not marked as a master (masters are the best match found with Table A) and sort the data by column A descending.

4.) Now add a new column into the first worksheet. Let's assume your worksheet 2 is A-P across and 26 columns high. Let’s also assume that you want to copy data from the 10th column in Worksheet 2 over to your data held in Worksheet 1 and that your Unique Ref column for worksheet 1 is held in column A.

Adding the command

 =IFERROR(VLOOKUP(A1,Sheet2!$A$1:$P$26,10,FALSE),"")

into the first cell in the new column will do the transfer. You can then copy/paste that cell into the remaining cells in the column. Note that you can find more information on this Excel function by simply googling it.

5.) You can also setup additional columns in Worksheet 1 for any other fields from Table A that you want to transfer.  Note that you might need to convert the left hand ID column in the matching Groups output if your Unique Ref field is numeric (it's output as a text field by default); the easiest way to do this is to simply copy/paste the field from Excel into and out of a standard text editor etc.

 

Alternatively a more technical user may simply choose to import the results into a DBMS system (e.g. Oracle, SQL Server) and implement some SQL code to do the merge.

 

  Previous Article Next Article
Was this article helpful?
0 out of 0 found this helpful

0 Comments

Please sign in to leave a comment.