Comparing and Matching data in 2 Excel files

Hey all I wonder if you can help at all. I am looking for a solution to a problem I am having with 2 XML files that have been output by an external program and I want to compare the data in the two files across a select number of columns in which I know the data can be matched as these columns are common between both files. I have done a lot of google searching and digging to try and find a solution but haven't managed to as yet. The only thing I seemed to find that helped was that the native XML files had poor structure and so opening them and exporting as XLSX files gave a far better file structure which can then be used for my purpose.

What I am trying to create is something that will convert an XML file into an XLSX file and then use the XLSX file to do some data matching between a pre-set number of columns but data must match across all the designated columns in order to be a match, it can?t just be 1 or 2 that match and that?s good enough it needs to be all. Once it has found a match it outputs the full row of each file into a new file which has 2 sheets in, each sheet represents one of the original files, within each sheet is a newly created column called Key which contains a unique reference number starting at 0000001 and going up which allows for a reference point to the matching rows as I will be doing some filtering of the data at a later stage but for now just being able to match up the data would be amazing.

Here is a list of the columns in each file that should be used purely for the purpose of matching data.

Withing the forward file: 'Back Result', 'Trades', 'PipStep', 'PipStepExponent', 'DelayTradeSequence', 'ReverseSequenceDirection', 'LotSize', 'LotSizeExponent', 'RsiTimeframe', 'RsiPeriod', 'RsiSellLevel', 'EmaTimeframe', 'EmaPeriods', 'EmaTrendRule', 'AdxTimeframe', 'AdxPeriod','AdxThreshold', 'AdxTrendRule', 'NewsAction'

Within the back file: 'Result', 'Trades', 'PipStep', 'PipStepExponent', 'DelayTradeSequence', 'ReverseSequenceDirection', 'LotSize', 'LotSizeExponent', 'RsiTimeframe', 'RsiPeriod', 'RsiSellLevel', 'EmaTimeframe', 'EmaPeriods', 'EmaTrendRule', 'AdxTimeframe', 'AdxPeriod','AdxThreshold', 'AdxTrendRule', 'NewsAction'

Once a match has been found using all of the above columns to match the data then that entire row should be output to the new _Matched file.

The full column list from each file is as follows.

Forward file: ?Pass?, ?Forward Result?, ?Back Result?, ?Profit?, ?Expected Payoff?, ?Profit Factor?, ?Recovery Factor?, ?Sharpe Ratio?, ?Custom?, ?Equity DD %?, ?Trades?, 'PipStep', 'PipStepExponent', 'DelayTradeSequence', 'ReverseSequenceDirection', 'LotSize', 'LotSizeExponent', 'RsiTimeframe', 'RsiPeriod', 'RsiSellLevel', 'EmaTimeframe', 'EmaPeriods', 'EmaTrendRule', 'AdxTimeframe', 'AdxPeriod','AdxThreshold', 'AdxTrendRule', 'NewsAction'

Back File: ?Pass?, ?Result?, ?Profit?, ?Expected Payoff?, ?Profit Factor?, ?Recovery Factor?, ?Sharpe Ratio?, ?Custom?, ?Equity DD %?, ?Trades?, 'PipStep', 'PipStepExponent', 'DelayTradeSequence', 'ReverseSequenceDirection', 'LotSize', 'LotSizeExponent', 'RsiTimeframe', 'RsiPeriod', 'RsiSellLevel', 'EmaTimeframe', 'EmaPeriods', 'EmaTrendRule', 'AdxTimeframe', 'AdxPeriod','AdxThreshold', 'AdxTrendRule', 'NewsAction'

Would anyone know of a method I could use to achieve this? Thanks in advance for any help or suggestions offered.

Hey AliGW,

Thank you for the warm welcome, I have attached some files to this post (hopefully) and below is an explanation of the files and their uses.

EURNZD_Forward_SAMPLE.xlsx & EURNZD_Back_SAMPLE.xlsx - These are the RAW files that are output from a third party program (MetaTrader 5) which is essentially all the data of a series of tests that have been run within that program exported to excel for the purpose of filtering etc. The columns that are highlighted in Yellow are the columns I am looking to match the data between as these Columns are common across both files, with the exception of one column being named differently between the two files which is the Back file results column. In the forward file it is referred to as 'Back Result' and within the back file it is simply labelled 'Result'

EURNZD_Matched_SAMPLE.xlsx - This would be the output file of the data once it has been matched and assigned a reference key. Once all data has been matched I will then run a macro within excel to bring some columns from the back sheet into the forward sheet and then filter the results based on a set of parameters, unfortunately in order to do this I first must match up the data and assign a key so that I can bring in columns using a VLOOKUP or similar method.

Hopefully this is of some help or gives a better understanding to my problem.

EURNZD_Back_SAMPLE.xlsxEURNZD_Forward_SAMPLE.xlsxEURNZD_Matched_SAMPLE.xlsx

If you could that would be greatly appreciated. Thank you

Just as an update and in case anyone ever has a similar issue I guess. I managed to get the problem solved using a python script. Probably could have been done in VBA also but I had a friend who was able to solve it using python so I will mark this case as SOLVED and I have included the code below in case it helps anyone else out.

Please Login or Register  to view this content.