0

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.

F Bert
  • 47

1 Answers1

0

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:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

and

http://msdn.microsoft.com/en-us/library/ee814735(v=office.14).aspx

Macros must be enabled for this to work!

Gary's Student
  • 19,396