Compare Column Order and Column Heads in multiple Files in a folder against a Template Worksheet/Workbook

[ragav_in]

Dear Friends and Forum users,

I am currently working on a activity that needs to be open multiple files within a folder and, check the Column order (Columns A to Column D) in first worksheet of each workbook against a pre-defined Column order (Column A to Column D) in a workbook called Template (and this is where the Macro is written and to be executed). I did some research on this and got this code which currently compares 2 worksheets in a particular workbook for the Column order and generates a Msgbox if the order does not match.

Source: Check order of columns
Macro to compare Column Order

Sub CompareFields()
    Dim templateColumns(), sourceColumns(), col As Integer

    templateColumns = Worksheets(1).Range("A1:D1").Value
    sourceColumns = Worksheets(2).Range("A1:D1").Value

    For col = 1 To UBound(templateColumns, 2)
        If templateColumns(1, col) <> sourceColumns(1, col) Then
            MsgBox "Source data not in the correct order"
            Exit For
        End If
    Next col
End Sub

I would want to know how to use this code to perform my activity of looping through multiple files in folder and perform this checking of order of columns. If it finds workbook that does not match the column order as mentioned in the Template, then, it should write that workbook name to a cell in another worksheet, so that at end of the macro execution, the column will have the list of workbooks from that folder that do not match the Columns order as mentioned in the Template worksheet.

I know this is a big ask, but I feel that this forum would provide me support in getting the resolution for the same. I thank everyone who puts their time and effort to go through this post and provide any suggestions that could help in accomplishing the task.

Many thanks in advance for all your support till date.

Thanks and grateful,
ragav_in

 

[ragav_in]

Try inserting this line of code

Option Compare Text

directly above this line

Sub CompareColumns()

Dear mumps, I did add the line as mentioned by you, however am getting the same result that when the case does not match that in the template sheet, the workbook name is captured by the code. Is there anything else that I am explicitly missing? Once again, a big thanks for your response.

Thanks
ragav_in

 

[Alex Blakenburg]

You might want to check the sheet that doesn't match for additional spaces in the heading most likely is trailing or leading space.
If you can't see anything obvious you could try adding the code below to your if statement as long as your result sheet has columns B & C available.

            If Join(Application.Transpose(Application.Transpose(.Sheets(1).Range("A1:J1").Value)), "|") <> _
                Join(Application.Transpose(Application.Transpose(srcWS.Range("A1:J1").Value)), "|") Then
                
                desWS.Cells(desWS.Rows.Count, "A").End(xlUp).Offset(1) = srcWB.Name
                
                ' temporary code for debugging - output joined result and length of joined result to check for extra blanks
                Dim desCell As Range
                Set desCell = desWS.Cells(desWS.Rows.Count, "A").End(xlUp)
                desCell.Offset(, 1) = Join(Application.Transpose(Application.Transpose(srcWS.Range("A1:J1").Value)), "|")
                desCell.Offset(, 2) = Len(Join(Application.Transpose(Application.Transpose(srcWS.Range("A1:J1").Value)), "|"))
                
            End If

 

[mumps]

If Alex's suggestion doesn't work out, perhaps you could use the XL2BB add-in (icon in the menu) to attach screenshots (not pictures) of your Results sheet and the source sheet that is causing the problem. Alternately, you could upload a copy of your two files to a free site such as www.box.com or www.dropbox.com. Once you do that, mark each file for 'Sharing' and you will be given a link to each file (de-sensitized if necessary) that you can post here.

 

[ragav_in]

If Alex's suggestion doesn't work out, perhaps you could use the XL2BB add-in (icon in the menu) to attach screenshots (not pictures) of your Results sheet and the source sheet that is causing the problem. Alternately, you could upload a copy of your two files to a free site such as www.box.com or www.dropbox.com. Once you do that, mark each file for 'Sharing' and you will be given a link to each file (de-sensitized if necessary) that you can post here.

Dear mumps and Alex, I am very much interested to share the documents, however after adding them in dropbox it is asking for email to share. However, I have added the template format and other report format using XL2BB here down so that you know what I am talking about.
This is my template report format

Cop of Defect Log-MrExcel.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Field1	Field2	Field3	Field4	Field5	Field6	Field7	Field8	Field9	Field10	Field11	Field12	Field13	Field14
2
Template

This is the sheet named Results in which the incorrect reports names are captured

Cop of Defect Log-MrExcel.xlsm
	A	B	C
1	List of Reports that do not match	Output Join Result	Length
2	Report1.xlsx	Field1|Field2|Field3|Field4|Field5|Field6|Field7|Field8|Field9|Field10|Field11|Field12|Field13|Field14	102
3	Report3.xlsx	Field1|Field2|Field3|Field4|Field5|Field6|Field7|Field8|Field9|Field10|Field11|Field12|Field13|Field14	102
Results

@Alex: The debug code you have given, is not able to capture the correct names from the individual worksheets, as you can see in Report 1 and Report 3, it shows the same column names, however they are different in the worksheet.

This is the Report 1 format

Report1.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Field1	Field2	Field3	Field4	Field5	Field6	Field7	Field8	Field9	NotField	Field11	Field12	Field13	Field14
2
SHeet1

This is Report 2 format

Report2.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Field1	Field2	Field3	Field4	Field5	Field6	Field7	Field8	Field9	Field10	Field11	Field12	Field13	Field14
2
Sheet2

This is Report 3 format

Report3.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Field1	Field2	Field3	Field4	FIELD5	Field6	Field7	Field8	Field9	Field10	Field11	Field12	Field13	Field14
2
Sheet2

As you see, Report 3 should not be identified as incorrect format, as only the case of the Column head is different. Please let me know if the above information is of any use to you to debug the issue.

Thanks in advance for your support as usual.

Thanks
ragav_in

 

[Alex Blakenburg]

I am not having the issue you are having can you post the "entire" code in your module from the top of the sheet where it says Option ....

The Option Compare Text should take care of the Case sensitivity. The below would be another way of doing it.

            If UCase(Join(Application.Transpose(Application.Transpose(.Sheets(1).Range("A1:J1").Value)), "|")) <> _
                UCase(Join(Application.Transpose(Application.Transpose(srcWS.Range("A1:J1").Value)), "|")) Then

 

[ragav_in]

I am not having the issue you are having can you post the "entire" code in your module from the top of the sheet where it says Option ....

The Option Compare Text should take care of the Case sensitivity. The below would be another way of doing it.

            If UCase(Join(Application.Transpose(Application.Transpose(.Sheets(1).Range("A1:J1").Value)), "|")) <> _
                UCase(Join(Application.Transpose(Application.Transpose(srcWS.Range("A1:J1").Value)), "|")) Then

Dear Alex and mumps, thanks for the above suggestion; this works now. This was what I was expecting on how to make sure that excel does not treat UCase <> LCase. FOr some reason thje Option Compare Text line at the very top of the Code snippet did not solve my purpose earlier, however this Ucase of Join will provide the desired results. In the mean time since I was trying some other ways, I came up with the below method to perform the same operation using Array

Arr1 = srcWS.Range("A1:N1").Value
    val1 = Arr1(1, 1) & "|" & Arr1(1, 2) & "|" & Arr1(1, 3) & "|" & Arr1(1, 4) & "|" & Arr1(1, 5) & "|" & Arr1(1, 6) _
    & "|" & Arr1(1, 7) & "|" & Arr1(1, 8) & "|" & Arr1(1, 9) & "|" & Arr1(1, 10) & "|" & Arr1(1, 11) _
    & "|" & Arr1(1, 12) & "|" & Arr1(1, 13) & "|" & Arr1(1, 14)

Arr2 = ActiveSheet.Range("A1:N1").Value
     val2 = Arr2(1, 1) & "|" & Arr2(1, 2) & "|" & Arr2(1, 3) & "|" & Arr2(1, 4) & "|" & Arr2(1, 5) & "|" & Arr2(1, 6) _
      & "|" & Arr2(1, 7) & "|" & Arr2(1, 8) & "|" & Arr2(1, 9) & "|" & Arr2(1, 10) & "|" & Arr2(1, 11) _
       & "|" & Arr2(1, 12) & "|" & Arr2(1, 13) & "|" & Arr2(1, 14)

If LCase(val2) <> LCase(val1) Then
    desWS.Cells(desWS.Rows.count, "A").End(xlUp).Offset(1) = srcWB.Name
End If

The above piece of code was almost working, but i Could not get enough time to test it on all possible combinations, which I will be doing soon. Once again, thanks a lot for your patience and the willingness to help and provide the solution.

Thanks,
ragav_in

 

[Alex Blakenburg]

In term of your alternative code above, you would normally do that using a For / Next look that you run through from 1 to 14.
Using Join as suggested by @mumps, would probably be faster.

In trying to get rid of the double Transpose, I found @pgc01's code here:
VBA join function
Which reduces your latest code to this:

    val1 = Join(Application.Index(srcWS.Range("A1:N1").Value, 1, 0), "|")
    val2 = Join(Application.Index(ActiveSheet.Range("A1:N1").Value, 1, 0), "|")

PS: It think mumps's post #6 would be more appropriate as being the solution.