In column A I have M&A deal descriptions that mention company names. In column B I have company names, for each of which I want to see where it is mentioned in column A.

What I'm having trouble with: Some of the strings in column B will be present as substrings in column A multiple times and I want to return all of the instances, not just one.


Try this rather small macro:

Sub DataLocator()
  Dim nA As Long, nB As Long, i As Long, j As Long, k As Long
  Dim v As String
  nA = Cells(Rows.Count, "A").End(xlUp).Row
  nB = Cells(Rows.Count, "B").End(xlUp).Row

  For i = 1 To nB
    v = Cells(i, "B").Value
    k = 3
    For j = 1 To nA
      If InStr(1, Cells(j, "A").Value, v) > 0 Then
        Cells(i, k).Value = Cells(j, "A").Address(0, 0)
        k = k + 1
      End If
    Next j
  Next i
End Sub

For example:

enter image description here

Macros are very easy to install and use:

  1. ALT-F11 brings up the VBE window
  2. ALT-I ALT-M opens a fresh module
  3. paste the stuff in and close the VBE window

If you save the workbook, the macro will be saved with it. If you are using a version of Excel later then 2003, you must save the file as .xlsm rather than .xlsx

To remove the macro:

  1. bring up the VBE window as above
  2. clear the code out
  3. close the VBE window

To use the macro from Excel:

  1. ALT-F8
  2. Select the macro
  3. Touch RUN

To learn more about macros in general, see:




Macros must be enabled for this to work!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.