Compare Excel files to identify extra columns

Mert

Hi everyone!

I am working with 100 Excel files. Supposedly, the template should be the same for all.

However I suspect that there might be some "cheeky" files with extra columns.

Would you know a way to compare the files and highlight those extra columns?

Thank you in advance!

Input

Accepted answers

gawa

hi @Mert

My idea is to get filed info of all of files by using Batch macro, and identify the extra columns.

Please refer to attached WF. (As it contains Directory tool, and Interface tool to replace path, it should not immediately work at your computer so please modify them)

1207685_gawa.yxzp

All comments

Qiu

@Mert
I have done something similar in work.
I had to compare the 100 Sheets between Excel A and B.

Basically I think we can first read in the all files only for Full path, then read in data for each sheet and drop the data with Field Info tool for each sheet in a Batch Macro,
Then I believe we can locate the "Cheeky" one.
Maybe you have some sample data, we can make something hopefully.

Mert

Hi @Qiu - Thank you for your quick reply.

Attached is a zipped folder containing 4 sample files. Two of them have the expected schema. Other two have an extra column each. Would this help?

Sample.zip

gawa

hi @Mert

My idea is to get filed info of all of files by using Batch macro, and identify the extra columns.

Please refer to attached WF. (As it contains Directory tool, and Interface tool to replace path, it should not immediately work at your computer so please modify them)

1207685_gawa.yxzp

Mert

@gawa - Thank you for sharing the workflow. Just to be sure, the Browse tool at the end displays common fields, is that right? If so, is there a way to reverse it to see "unique" column(s) per file?

gawa

hi @Mert

In my WF, Browser tool being connected from L anchor of Join tool shows "uncommon" fields that is extra columns you want to find.

If you see J anchor of Join tool, you can know "common" fields that all of the files have in common.

Mert

@gawa - My apologies that I did not get back to your message earlier, due to busy Q4! However your solution helped me immediately on the day.

I would like to thank you for providing a solution promptly. Cheers!

gawa

@Mert Glad to hear that. No problem, everybody knows Q4 is the busiest season!

Quick Links

This months top contributors

LanisC 335

hhaigh1 280

FláviaB 262

JORGE4900 235

caltang 177