VBA/ Excel method for searching/ matching 60+ values in a single cell

Question

I am doing a migration between too systems. The old system used Tags to classify products, the new system introduces Type.

The data is available to me as a CSV of several thousand rows I've loaded into Excel. Here's an example of the format.

Col A      | Col B | Col C
Product    | Type  | Tags
Samsung S5 |       | Android, Samsung, 5.1" Screen
Sony Z3    |       | Android, Bluetooth, Sony, 5.2" Screen
LG G3      |       | Android, LG, 5.5" Screen

I want to be able to populate Column B with a single tag from Column C. I could do it like this:

A1: =IF(SEARCH("Sony",B2),"Sony", IF(SEARCH("Samsung",B2),"Samsung",etc))

However I want to search/ match over 60 individual Tags in Column C into single values in Column B so this approach rapidly becomes unmanageable.

Is there another approach using Excel functions or would I have to use VBA?

I haven't used VBA in many years so any examples/ pointers would be appreciated.

Vasily Ivoyzha Vasily Ivoyzha · Accepted Answer · 2015-01-19T00:12:29

Is there another approach using Excel functions or would I have to use VBA?

IMHO VBA, try this:

First variant (slower than the second variant)

Sub test()
Dim oCellTag As Range, oCellSource As Range
For Each oCellTag In Sheets("Tags").Range("A1:A3") 'Range with Tags in one sheet
    For Each oCellSource In Sheets("Source").Range("C2:C4") 'Range with data for search tags in another sheet
        If UCase(oCellSource.Value) Like "*" & UCase(oCellTag.Value) & "*" And oCellTag.Value <> "" Then 'if cell contain tag
            Sheets("Source").Cells(oCellSource.Row, oCellSource.Column - 1).Value = oCellTag.Value
        End If
    Next
Next
End Sub

Second variant (fast)

Sub test2()
Dim oCellTag As Range, oCellSource As Range, KeySource, KeyTag
Dim Source As Object: Set Source = CreateObject("Scripting.Dictionary")
Dim Tags As Object: Set Tags = CreateObject("Scripting.Dictionary")
'Grab the dates from the WorkSheets
For Each oCellTag In Sheets("Tags").Range("A1:A3")
    If oCellTag.Value <> "" Then
        Tags.Add oCellTag.Row, oCellTag.Value
    End If
Next
For Each oCellSource In Sheets("Source").Range("C2:C4")
    If oCellSource.Value <> "" Then
        Source.Add oCellSource.Row, oCellSource.Value
    End If
Next
'Match
For Each KeyTag In Tags
    For Each KeySource In Source
        If UCase(Source(KeySource)) Like "*" & UCase(Tags(KeyTag)) & "*" Then
            Sheets("Source").Cells(KeySource, 2).Value = Tags(KeyTag)
        End If
    Next
Next
End Sub

VBA/ Excel method for searching/ matching 60+ values in a single cell

1 Answers