Categorize each row in a group (1 time) based on the contents of String? (Excel)

Question

Difficult problem ... let me first explain, maybe there is a better solution, rather than using iterative calculations:

(Link to book)

Image display example (to show what I'm working with)

Problem:

You have 4000+ lines and would like to classify them into predefined groups (based on the contents of String).

• Each row should be assigned to only one group. (i.e., the “55 gallon drum mixer” will be indicated in the “faucet” column, as it contains the word “faucet”.)

• After classification in a group, the row will not be classified under any other groups. (i.e., the “55 gallon drum mixer” will not be classified as “drum” if it has already been classified as “tap”).

• It really doesn't matter which group each line belongs to while it is categorized.

Note: (I almost found a solution using iterative calculation, but it doesn’t quite work).

DECISION:

I approached the problem:

• Count the number of repetitions of a String string (column A) on a worksheet using the formula:

   Formula: =COUNTIF($E$2:$IA$10000,A3)
  

  • This formula is in column C.

• , , Group (, "faucet" , "beer", "gallon", "kitchen" ..)... (.. , ).

    Formula: =IF(C3<1,IF(IFERROR(SEARCH("faucet",A3),0)>0,A3,""),"")
  

• 4000 C "".

, , :

• ( Times Dup'd 0 1)...

2. "Times Dup'd" 0 1, String "".

   ---

? ( , , , 1 "" ... , - "", ...)

!

Answer 1

. .

Sub byGroup()
    Dim g As Long, s As Long, aSTRs As Variant, aGRPs As Variant

    appTGGL bTGGL:=False

    With Worksheets("Sheet1")
        aSTRs = .Range(.Cells(2, 1), .Cells(Rows.Count, 1).End(xlUp)).Value2
        With .Range(.Cells(1, 5), .Cells(Rows.Count, 1).End(xlUp).Offset(0, Application.Match("zzz", .Rows(1)) - 1))
            .Resize(.Rows.Count, .Columns.Count).Offset(1, 0).ClearContents
            aGRPs = .Cells.Value2
        End With

        For s = LBound(aSTRs, 1) To UBound(aSTRs, 1)
            For g = LBound(aGRPs, 2) To UBound(aGRPs, 2)
                If CBool(InStr(1, aSTRs(s, 1), aGRPs(1, g), vbTextCompare)) Then
                    aGRPs(s + 1, g) = aSTRs(s, 1)
                    Exit For
                End If
            Next g
        Next s

        .Cells(1, 5).Resize(UBound(aGRPs, 1), UBound(aGRPs, 2)) = aGRPs

    End With

    appTGGL
End Sub

Public Sub appTGGL(Optional bTGGL As Boolean = True)
    Debug.Print Timer
    Application.ScreenUpdating = bTGGL
    Application.EnableEvents = bTGGL
    Application.DisplayAlerts = bTGGL
    Application.Calculation = IIf(bTGGL, xlCalculationAutomatic, xlCalculationManual)
End Sub

( 1-2 ).

. , "55- " drum, , , 1.

​​ Excel (.XLSB) .

Answer 2

-, . Jeeped-, . , , :

Sub sikorloa()

Dim r As Integer
Dim c As Integer
Dim LastRow As Integer
Dim LastCol As Integer
Dim strng As String
Dim grp As String

Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False

LastRow = Range("A" & Rows.Count).End(xlUp).Row
LastCol = Cells(1, Cells.Columns.Count).End(xlToLeft).Column

For r = 3 To LastRow
    If Cells(r, 1).Value <> "" Then
        strng = Cells(r, 1).Value
        For c = 5 To LastCol
            grp = Cells(1, c).Value
            If InStr(strng, grp) > 0 Then
                Cells(r, c).Value = Cells(r, 1).Value
                Exit For
            End If
        Next c
    End If
Next r

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

End Sub