Comparing Data - Statussing

Hi

I am trying to produce a dashboard from a workbook where new data is uploaded every 2 weeks. The format of the tests and outputted data is identical each fortnight and comes from sequential models - however it is very difficult and time consuming to identify which model each batch of data belongs to and whether or not each line item is new or old. I have attached a sample workbook with two worksheets displaying data from 2 sequential models - Essentially the two column values that uniquely define a row item (or a "clash" as per what i'm testing) are the CR1 and CR2 Element ID numbers (concatenating these values is not an option as my testing results can reverse the order of the tests in later models, flipping the concatenated value). The third worksheet is both data sets combined. Understandably this may not make sense - so simplified - How can I compare values in 2 columns from one worksheet to the same 2 columns in a subsequent worksheet? Ideally the output would identify duplicates and unique values - and from there I could determine which row is "old" or "new" data and that would contribute toward a dashboard output demonstrating new and old "clashes" for that fortnight / review cycle. I have read about index, match and IF possibilities but cannot seem to translate their function to what I am trying to achieve.


Any assistance would be greatly appreciated.

Tom

Hi Tom,

It seems easiest for you to just add an additional column in 'model 1' and 'model 2' that helps to identify the data when you combine it in the 'Federated' sheet.

That could be a unique number for each row that also tells you the chronology of the data. For example:

Add column L to 'Model 1' and 'Model 2' sheets that are called: ID
The sequence could start at 100001 for 'Model 1' and 200001 for 'Model 2'. That way, when you combine this data too, in sheet 'Federated', you'll always know how old the data is and which sheet it comes from.

Does this makes sense in your situation?

Hi Kasper,

Thanks for your response, the only issue is that because I have duplicates rows in my data set that i don't know how to delete because they are only identifiable by two values in two different columns, I would be assigning a unique ID to duplicate rows.. but it does help me keep track so thanks for the idea. Any other recommendations are welcomed.

Can you compare two columns from one worksheet with two columns in another worksheet? Alternatively, can you compare one row from one worksheet to a row from another worksheet?

Thanks,

Tom

Sorry for the confusion Kasper, but it turns out I can concatenate the two element ID columns to create a unique "clash" ID which will help me a lot. This way I can compare single columns next to each other in a federated worksheet.

Any other ideas welcomed.

Tom