Forum Discussion

Anonymous's avatar
Anonymous
Jun 07, 2022

how to do this partial match? Thanks in advance!

Hi, could anybody give me some insight of how to do partial match?

my situation is as below:

I have thousands row of column b, and column d is the things I will be deleting:

instead of searching each cell in column B from column d:

e.g., 1.seach if there is any bbb in column b, 

       2. found b2 contains bbb,

       3.deleting bbb in b2

 

 will there be any formula help me to accomplish this procedure?

All I need is partial match.

Best,

 

 

 

excel
Formulas and Functions

5 Replies

    • Anonymous's avatar
      Anonymous
      Jun 07, 2022
      thank you! I think this is what I was asking for: but would you mind telling me what should I revise if I have like hundreds of row in column D?
      my situation is I have thousand rows in column B, and hundred rows in column D, do I have to edit each rows of column E in your attached file?
      or could I just maybe perform partial match in column E?
      Thanks a million~
      • OliverScheurich's avatar
        OliverScheurich
        Gold Contributor
        Jun 08, 2022

        Deleted 

        Sub combine_delete()
    
Dim i As Long
Dim j As Long
Dim k As Long
Dim mypos As Long
Dim rowsB As Long
Dim rowsD As Long

rowsB = Range("B" & Rows.Count).End(xlUp).Row
rowsD = Range("D" & Rows.Count).End(xlUp).Row

For i = 2 To rowsB

For k = 2 To rowsD

mypos = InStr(1, Cells(i, 2), Cells(k, 4))

If mypos > 0 Then

Cells(i, 7).Value = Replace(Replace(Cells(i, 2), Cells(k, 4), ""), ",,", ",")

If Left(Cells(i, 7), 1) = "," Then
Cells(i, 7).Value = Right(Cells(i, 7), Len(Cells(i, 7)) - 1)

Else

    If Right(Cells(i, 7), 1) = "," Then
    Cells(i, 7).Value = Left(Cells(i, 7), Len(Cells(i, 7)) - 1)
    
    Else
    End If

End If

Else
End If
Next k

Next i


End Sub

        Maybe with this code. In the attached file you can click the button in cell E2 to start the macro. You can enter additional data in columns B and D.

Resources