Search code examples
excelexcel-formulaexcel-2013vba

Excel Compare two columns for matches or differences in the same row based on the header

There are 45 columns on Sheet1
Every time the column are not coming in the same place, in the Sheet1 there are two important columns that is Work Country and Base Country, where I am checking if values from both columns are same or different: if they are same, the output in column Status should be Local, Expat otherwise.
But when I tryed calculating my formula the result was #Name?
Can you help me with this issue? I have attached the screen shot of my worksheet.
My Excel file includes VBA Modules as well, so I am ok with either formula or VBA code.

This is the formula I have used: 

=IF(Work Geography,A2)=IFS(Work Country,B2)

enter image description here

Solution

  • There could be multiple reasons for this error:

    • IFS function is not defined in MS Office Professional Plus 2013, you are using a function that is not enabled in your version of Excel.
    • Your formula has a reference to a name that is not defined in Excel, you are obviously using named range (Work Geography and Work Country) but you could forget to define them.
    • There is a typo, it is either in Work Geography or Work Country or both, because name can't hold space characters.
    • Logic is flawed, using IF function on values which are not boolean could lead to unepxpected results.

    Formula I would use:

    =IF(A2=B2, "Local", "Expat")

    Thanks to @Zac for clarifications.
    This is what I came up with:

    Option Explicit
Sub CheckCountries()
    Dim lRow As Long
    Dim MySheet As Worksheet
    Dim rngGeo As Range, rngCountry As Range, rngStatus As Range
    Dim rngData As Range

    '    Define Sheet1
    Set MySheet = ThisWorkbook.Worksheets("Sheet1")

    '    I assumed headers are constantly on the first row
    '    Find cells with needed headers
    Set rngGeo = MySheet.Rows(1).Find("Work Geography", , xlValues, xlWhole, xlByColumns)
    Set rngCountry = MySheet.Rows(1).Find("Work Country", , xlValues, xlWhole, xlByColumns)
    Set rngStatus = MySheet.Rows(1).Find("Status", , xlValues, xlWhole, xlByColumns)

    '    Your big table, excluding headers
    With MySheet.Range("A1").CurrentRegion
        Set rngData = .Offset(1, 0).Resize(.Rows.Count - 1, .Columns.Count)
    End With
    
    '    Define needed columns
    Set rngGeo = Intersect(rngData, rngGeo.EntireColumn)
    Set rngCountry = Intersect(rngData, rngCountry.EntireColumn)
    Set rngStatus = Intersect(rngData, rngStatus.EntireColumn)

    '    Calculate
    rngStatus.Value _
        = Evaluate("=IF(" & rngGeo.Address & "=" & rngCountry.Address & ", " & _
                        """Local"", " & _
                        """Expat"")")
End Sub