I have an Excel template with columns in the following order:
Clientname Date Id Campaign Websitename Frequency Clicks Mediacost My data source has the same fields but always in different order e.g.:
websitename Frequency Clicks Mediacost Clientname Date Id Campaign I need some functionality to check if the order in the data source file is correct.
This will compare the entire first rows of two sheets in one go:
Sub Test() Dim wb As Excel.Workbook Dim Sheet1Header As Excel.Range Dim Sheet2Header As Excel.Range Set wb = ThisWorkbook Set Sheet1Header = wb.Worksheets("Sheet1").Rows(1) Set Sheet2Header = wb.Worksheets("Sheet2").Rows(1) If Join(Application.Transpose(Application.Transpose(Sheet1Header.Value)), ",") = _ Join(Application.Transpose(Application.Transpose(Sheet2Header.Value)), ",") Then MsgBox "Match!" Else MsgBox "No Match" End If End Sub EDIT: After posting this I read Simoco's comment and Tim Williams answer. It would be good to change the commas I used as the 2nd Join argument to Chr(0)s, or something as obscure. With the commas, it's possible to get a false match if the headers also include commas.
How about this? Imagine two sheets in a workbook with headers in A1:D1. This will compare the order and show a message if not in the same 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 think you have asked the wrong question.
You tell us columns in your data source are not in the same sequence as those in your template. So the columns in the data source will never match those in your template. What you want code that will match columns by name and build an array that relates source columns to destination columns.
The code below builds array ColSrcToDest() and then output the contents to the Immediate Window. For your example headings it outputs:
Source Destination 1 5 2 6 3 7 4 8 5 1 6 2 7 3 8 4 This means data in source column 1 should be copied to destination column 5.
The code checks for mismatches and builds strings listing names in one sheet but not the other.
Option Explicit Sub MatchCols() Dim ColDestCrnt As Long Dim ColDestLast As Long Dim ColDestNameMissing As String Dim ColSrcCrnt As Long Dim ColSrcLast As Long Dim ColSrcNameNew As String Dim ColSrcToDest() As Long Dim Found As Boolean Dim HeadDest As Variant Dim HeadDestInSrc() As Boolean Dim HeadSrc As Variant With Worksheets("Source") ' Find last used column in header row ColSrcLast = .Cells(1, Columns.Count).End(xlToLeft).Column ' Load source header row to variant HeadSrc = .Range(.Cells(1, 1), .Cells(1, ColSrcLast)).Value End With With Worksheets("Destination") ' Find last used column in header row ColDestLast = .Cells(1, Columns.Count).End(xlToLeft).Column ' Load source header row to variant HeadDest = .Range(.Cells(1, 1), .Cells(1, ColDestLast)).Value End With ' Size array that will relate source columns to destination columns ReDim ColSrcToDest(1 To ColSrcLast) ' Size array that will record destination headings that found in source headings ReDim HeadDestInSrc(1 To ColDestLast) ColSrcNameNew = "" For ColSrcCrnt = 1 To ColSrcLast Found = False ' Search destination headings for current source heading For ColDestCrnt = 1 To ColDestLast If LCase(HeadDest(1, ColDestCrnt)) = LCase(HeadSrc(1, ColSrcCrnt)) Then Found = True Exit For End If Next If Found Then ' Current source heading found amid destination headings ' Record destination column for this source column ColSrcToDest(ColSrcCrnt) = ColDestCrnt ' Record current destination column found HeadDestInSrc(ColDestCrnt) = True Else ' Current source heading not found amid destination headings ' Add heading to list of new source headings If ColSrcNameNew <> "" Then ColSrcNameNew = ColSrcNameNew & " " End If ColSrcNameNew = ColSrcNameNew & HeadSrc(1, ColSrcCrnt) End If Next ColDestNameMissing = "" For ColDestCrnt = 1 To ColDestLast If Not HeadDestInSrc(ColDestCrnt) Then If ColDestNameMissing <> "" Then ColDestNameMissing = ColDestNameMissing & " " End If ColDestNameMissing = ColDestNameMissing & HeadDest(1, ColDestCrnt) End If Next ' If ColSrcNameNew <> "", there are columns in the source data not present ' in the destination heading. This may be acceptable if you are selecting ' interesting columns from a fuller dataset. ' If ColDestNameMissing <> "", there are destination columns with no matching ' source column. I assume this will be unacceptable. ' The data from source column N goes to destination column ColSrcToDest(N) ' If ColSrcToDest(N) = 0, there is no destination column for source column N. If ColSrcNameNew <> "" Then Debug.Print "ColSrcNameNew = " & ColSrcNameNew End If If ColDestNameMissing <> "" Then Debug.Print "ColDestNameMissing = " & ColDestNameMissing End If Debug.Print "Source Destination" For ColSrcCrnt = 1 To ColSrcLast Debug.Print Right(Space(5) & ColSrcCrnt, 6) & " " & _ ColSrcToDest(ColSrcCrnt) Next End Sub I know this is pretty old so I'm just offering this idea for anyone else with a similar issue. This solution relies on the incoming data headers being exactly the same as the target headers but just in a different order. Use Advanced Filter where the list range is the incoming data and the Copy to Range is the target headers (no criteria range). In VBA, it would look something like this:
ActiveSheet.Range("A1:H23").AdvancedFilter _ 'The Source Data Action:=xlFilterCopy, _ CopyToRange=ActiveSheet.Range("J1:Q1") 'The Target Headers If you are doing this in VBA, the Source and Target can be on different sheets. If you are doing it in Excel, they have to be on the same sheet. Hope this helps someone.
