Iterative Data Comparison

[Kb7980]

HI

I am looking to compare data between 2 files - Pre and Post. 

1.Col A to Col D in both files are columns which form key to compare data 

2. Col E and Col F are being compared 

3. When data matches , i am looking to populate "Match" in a new column for Col E and Col F

4.When data dsnt match, i am looking to populate "MisMatch"

5. When there is new key in Post, i am looking to populate N/A

6. Keys in both Pre and Post can be repetitive.

The bigger problem in this situation is that instead of 2 target columns that I need to compare, I actually have 100 columns that need to be compared. With little experience that I have, it looks like a batch macro might have to be used here but I have no idea on how to implement this. Any help is appreciated

 

[mceleavey]

Hi @Kb7980 ,

can you post the data so we can help? A photograph doesn't help us at all.

M.

[Kb7980]

Attaching the files.

[AZuc]

@Kb7980 , see if it helps.

[Kb7980]

Hi Azuc /all

Sorry , think I did not explain my issue well. I combined post and output files earlier but have segregated them now to be more clear

Have attached 3 files here

PRE

POST

OUTPUT

I am looking to compare data between 2 files - Pre and Post and populate the results as in Output file (attached here)

1.Col A to Col D in both files are columns which form key to compare data 

2. Col E and Col F are being compared 

3. When data matches , i am looking to populate "Match" in a new column for Col E and Col F

4.When data dsnt match, i am looking to populate "MisMatch"

5. When there is new key in Post, i am looking to populate N/A

6. Keys in both Pre and Post can be repetitive.

7. Results have to be populated for all rows in Post sheet

The bigger problem in this situation is that instead of 2 target columns that I need to compare, I actually have 100 columns that need to be compared. With little experience that I have, it looks like a batch macro might have to be used here but I have no idea on how to implement this. Any help is appreciated

[AZuc]

@Kb7980 

I see only 3 differences between your output and the result of the workflow

1 - I've generated columns for Pre and Post Values plus Status. Your output show only Post Value. In this case you could ignore the treatment I've made to Pre Values and remove it as an input to Union Tool.

2 - The order of records respect the order of the Post file. If its the case, adding a record ID and sorting in the end may solve it.

3 - Column names are slightly different. A Dynamic Rename could help here

Please confirm you need these 3 changes?

Is there anything else I've missed?

[Kb7980]

Hi Andre

I see only 3 differences between your output and the result of the workflow

1 - I've generated columns for Pre and Post Values plus Status. Your output show only Post Value. In this case you could ignore the treatment I've made to Pre Values and remove it as an input to Union Tool.

Kb - Yes, only want to show Post value.

2 - The order of records respect the order of the Post file. If its the case, adding a record ID and sorting in the end may solve it.

Kb - Yes that is the case.

3 - Column names are slightly different. A Dynamic Rename could help here

Kb- Agreed to this also.

Kb- I did have a look at your workflow and the only other thing that want to confirm is that this can be extended to a scenario where instead of 2 columns to be comapred, I actually have 100. Think your workflow will be able to takr care of that with small changes. Hope that is correct understanding.

Thanks regards

Kb

[AZuc]

@Kb7980 

Yes, the WF was built to work with as many columns you have, only making sure that ColA-D exists and that will be a column ending with Status for each extra column you have. Let me send a V2 removing the original Status Columns and recalculating them.

[AZuc]

@Kb7980 there it is

[Kb7980]

Thanks Andre. I have been testing this on the data that I have and it seems all ok except for when the Col A to Col D combinations are being repeated either in Pre or Post files.

I have attached files with such records added and for these the workflow kind of stutters. Appreciate your help but did not realize there would be such data that can cause issues.

There is possibility of such data combinations getting repeated in both pre and post files.