Comparing the column names for many sheets of two Excel files

[esoestemp]

Hello experts,

I need your help in finding a solution of the following task.

I have two Excel files (X and Y) with around 100 sheets (let's name them A, B, C...).

The names of the sheets are same for the two files (X.A = Y.A).

For every corresponding sheets(X.A = Y.A; X.B = Y.B) in the these two files I want to check if the column names(the first row) are same (X.A.Column1Name = Y.A.Column1Name..).


Please let me know if you want more details to describe the task more clearly.

Thanks in advance!

 

[elmer007]

This is bit complicated, but I have a possible solution that's a bit complicated to match. This is a little confusing since the example worksheets are A,B,C,etc. and columns are also identified by letter, but here goes.

In another spreadsheet (can be another workbook), while having files X and Y open (due to the INDIRECT formula), try the following setup. Note that in my example, there are files X and Y, and each file has 3 tabs ("A", "B", "C"), and each tab has 4 columns with column names.


There are 3 tables described. The top table finds the names of the columns in file X, the middle table finds the names of the columns in file Y, and the bottom table finds any differences. So, in my example, the top table is in the range A1:E4, the middle table is in the range A6:E9, and the bottom table is in the range A12:E14.

Top table:
In cell A1, I have the name of a file, "X". In cells B1:E1, I have the alphabet starting with "A" (e.g., B1="A", C1="B", D1="C").
In cells A2:A4, I have the names of the tabs (e.g., A2="A", A3="B", A4="C").
In cell B2, I have the formula =INDIRECT("["&$A$1&".xlsx]"&$A2&"!"&B$1&"1") . Copy this into the range B2:E4.

Middle table:
This table is the same as the top table, except that it begins in A6 and goes right and down from there.
Also, the formula in B7 would be =INDIRECT("["&$A$6&".xlsx]"&$A7&"!"&B$1&"1") . Copy this into the range B7:E9.

Bottom table:
This table compares the top and middle tables. For the cells in column A (A12:A14), I have used the same tab names as the top two tables ("A", "B", "C"). Then, in cell B12, put =B2=B7 . This will check to see if the column names between the two files are the same. Copy this into the range B12:E14. Anywhere that shows "FALSE" means that the files have different column names.



This is much more confusing to describe than to do. Let me know if this doesn't work or if you have any questions.

 

[esoestemp]

This is bit complicated, but I have a possible solution that's a bit complicated to match. This is a little confusing since the example worksheets are A,B,C,etc. and columns are also identified by letter, but here goes.

In another spreadsheet (can be another workbook), while having files X and Y open (due to the INDIRECT formula), try the following setup. Note that in my example, there are files X and Y, and each file has 3 tabs ("A", "B", "C"), and each tab has 4 columns with column names.


There are 3 tables described. The top table finds the names of the columns in file X, the middle table finds the names of the columns in file Y, and the bottom table finds any differences. So, in my example, the top table is in the range A1:E4, the middle table is in the range A6:E9, and the bottom table is in the range A12:E14.

Top table:
In cell A1, I have the name of a file, "X". In cells B1:E1, I have the alphabet starting with "A" (e.g., B1="A", C1="B", D1="C").
In cells A2:A4, I have the names of the tabs (e.g., A2="A", A3="B", A4="C").
In cell B2, I have the formula =INDIRECT("["&$A$1&".xlsx]"&$A2&"!"&B$1&"1") . Copy this into the range B2:E4.

Middle table:
This table is the same as the top table, except that it begins in A6 and goes right and down from there.
Also, the formula in B7 would be =INDIRECT("["&$A$6&".xlsx]"&$A7&"!"&B$1&"1") . Copy this into the range B7:E9.

Bottom table:
This table compares the top and middle tables. For the cells in column A (A12:A14), I have used the same tab names as the top two tables ("A", "B", "C"). Then, in cell B12, put =B2=B7 . This will check to see if the column names between the two files are the same. Copy this into the range B12:E14. Anywhere that shows "FALSE" means that the files have different column names.



This is much more confusing to describe than to do. Let me know if this doesn't work or if you have any questions.

Hi,
Thanks for the proposal and the time spent helping me.
However there is something which I forgot to mention - the workbooks contains 110 sheets and in most of the cases they have 20-30-40 columns each.
So that certainly should be done via VBA.

 

[esoestemp]

Just one clarification for the guys who are trying to help - the workbooks contains 110 sheets and in most of the cases they have 20-30-40 columns each.