Comparing the column names for many sheets of two Excel files

esoestemp

New Member
Joined
Apr 14, 2015
Messages
7
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!
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,567
Messages
6,114,344
Members
448,570
Latest member
rik81h

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top