LookUp Value and Concatenate All Found Results

[Rick Rothstein]

You are correct in your interpretation of what I am doing, and in any other case I would prefer your suggested output, but in this particular case I need the output in every cell.

Okay, here's the solution. Rather than create a new procedure, I simply wrote a macro that repeatedly calls my LookUpConcat function (while it can be used as a UDF, it is also a perfectly good VB function as well). So, put the LookUpConcat function in a module and then put this macro in the same module...

Sub LookUpAndConcatenate()
  Dim Cell As Range, LastRow As Long, ResultRow As Long
  Dim SearchRange As Range, ReturnRange As Range
  Const SearchCol As String = "B"
  Const ReturnCol As String = "AU"
  Const ResultCol As String = "D"
  Const StartRow As Long = 3
  LastRow = Cells(Rows.Count, SearchCol).End(xlUp).Row
  Set SearchRange = Cells(StartRow, SearchCol).Resize(LastRow - StartRow + 1)
  Set ReturnRange = Cells(StartRow, ReturnCol).Resize(LastRow - StartRow + 1)
  For Each Cell In SearchRange
    Cells(Cell.Row, ResultCol).Value = LookUpConcat(Cell.Value, SearchRange, ReturnRange)
  Next
End Sub

Then, when you want to fill the cells, just run the above LookUpAndConcatenate macro. Note that I used Const statements (they declare constants) to set the various parameters from your worksheet so that you can change them later on in case things change on your worksheet for some reason.

[Rick Rothstein]

Thank you, this works brilliantly - being able to adjust the three column names turned out very useful in the end as I needed to change the output destination.

The macro takes a bit longer to run than just using the UDF on the column repeatedly, but the result is far easier to work with and the spreadsheet is more stable.

You are quite welcome... I am glad everything ended up working out for you.