CODEWITHSUNDEEP
excelvba
Treckez
Treckez

Reputation: 11

Compare two columns, if a match replace one with another cell beside

I am trying to add category codes to text categories in an excel spreadsheet. I have a column A with all the category names in text, including duplicates. I have column B with all the category names in text, not including duplicates and I have column C which has the category codes not including duplicates. Each code in column C alligns with its category in column B For example:

A:
Domestic Ware

B:
Agri Tools

C:
051

051 is the code for agri tools. I want to take the string in column A, Search for this string in column B and then replace the string that was in column A with the code in C.

Is there anyway to do this using VBA or excels built in functions?

Upvotes: 1

Views: 9694

Answers (2)

MikeD
MikeD

Reputation: 8941

If column A contains a list of Category names including duplicates, and columns B & C contain (the same) category names and Category ID's without duplicates (i.e. a "category code table"), you can

  • in column D (or maybe better insert a new column between A and B)

  • for each entry in A you enter =VLOOKUP(Ax,$B$y:$C$z,2,FALSE)

whereby

x = current row number

y = starting row of "category code table"

z = end row of "category code table"

You make the coord's of the lookup table absolute to prevent y & z being changed as you copy the formula down.

Upvotes: 1

Gary's Student
Gary's Student

Reputation: 96771

Since you want to replace values in column A, give this small macro a try:

Sub FixColumnA()
    Dim nA As Long, nB As Long, v As Variant
    Dim i As Long, j As Long
    nA = Cells(Rows.Count, "A").End(xlUp).Row
    nB = Cells(Rows.Count, "B").End(xlUp).Row
    For i = 1 To nA
        With Cells(i, "A")
            v = .Value
            For j = 1 To nB
                If v = Cells(j, "B").Value Then
                    .Value = Cells(j, "C").Value
                End If
            Next j
        End With
    Next i
End Sub

Upvotes: 2

Related Questions