Sum total by partial text

[YasserKhalil]

Hello everyone

I have a column of raw data with some items (Column B) and I already have other items in Column K which I need to search partially for them in Column B and sum the total for the suitable item
The attachment will be better for clarification. The desired output in Column L
Thanks advanced for help

Posted here: https://www.excelforum.com/excel-progra ... -text.html" onclick="window.open(this.href);return false;

[HansV]

I'd clean up the data...

[YasserKhalil]

What do you mean? ..

[HansV]

Currently, column A and column K use an inconsistent mix of "-" and "_". You should make that consistent first. Next, get rid of the extra "14" in column A (and presumably other things like that in the real data).

[YasserKhalil]

I need to deal with the data as it is as real data consists of thousands of rows and the process of cleaning up will take too much time.
Can regular expressions do such task .. I mean to depend on patterns?

[YasserKhalil]

I got it solved by Karedog's code which depends on regular expressions as I expect.

Code: Select all

Sub Test()
  Dim a, b, i As Long, j As Long, v
  With Sheets("Sheet1")
    a = .Range("B4:G10").Value
    b = .Range("K8:K11").Value
    With CreateObject("VBScript.RegExp")
      .IgnoreCase = True
      For i = 1 To UBound(b, 1)
          v = Split(b(i, 1), "_")
          b(i, 1) = 0
          .Pattern = ".*" & v(0) & ".*[-_].*" & v(1) & ".*"
          For j = 1 To UBound(a, 1)
              If .Test(a(j, 1)) Then b(i, 1) = b(i, 1) + a(j, 6)
          Next j
      Next i
    End With
    .Range("N8").Resize(UBound(b, 1), UBound(b, 2)).Value = b
  End With
End Sub

Thank you very much Mr. Hans