Check order of columns

Question

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.

Answer 1

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

Answer 2

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.

Answer 3

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

Answer 4

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.