How to populate a column based on cell data matching Items in list

Question

I have a table in excel like the example below:

Box Size	Header	Header	Header	Header	Header	Header	Header	Header	Header	Description
Full										Apples
Full										Oranges
Half										Grapes
Half										Lemons

Box size is in column A, Description is in column K. The box size is automatically "Full" for all rows and I must change them to say "Half" manually. I have a list of items on Sheet2 called "HalfBox" like the example below:

Half Items
Grapes
Lemons

I am trying to find a macro that will compare the descriptions in column K with the list in Sheet2. If a match is found I would like to automatically change the value in the A cell to say "Half".

Answer 1

Option 1:

• Load source data into arrData1, load items list into arrData2
• Use StrComp to compare the description and items (case-insensitive)
• If Half Items is a large list, loading it into a Dictionary object can improve the efficiency of comparation

Microsoft documentation:

StrComp function

Option Explicit
Sub Demo()
    Dim i As Long, j As Long
    Dim  arrData2, rngData1 As Range, rngData2 As Range
    Const DES_COL = 11
    Set rngData1 = Sheets("Sheet1").Range("A1").CurrentRegion
    Set rngData2 = Sheets("Sheet2").Range("A2").CurrentRegion
    arrData1 = rngData1.Value
    arrData2 = rngData2.Value
    For i = LBound(arrData1) + 1 To UBound(arrData1)
        For j = LBound(arrData2) + 1 To UBound(arrData2)
            If StrComp(arrData1(i, DES_COL), arrData2(j, 1), vbTextCompare) = 0 Then
                arrData1(i, 1) = "Half"
                Exit For
            End If
        Next j
    Next i
    rngData1.Value = arrData1
End Sub

---

Option 2:

• Apply formulas on col A, eg. the formula on Sheet1 A2

=IF(COUNTIF(Sheet2!A:A,Sheet1!K2)>0,"Half","Full")