LookUp Value and Concatenate All Found Results

[shaft1]

Hi

I have a beginner with the VB codes ad macros but slowly getting my head around it and i am enjoying no matter how much i struggle with these.

i need to look up two columns (columns F & G) and find matching values in columns C and A respectively. And return multiple results (from column B) concatenated as shown in the attached spreadsheet.

I am most likely to have thousands of rows of these sort of data and have the need to automate as you can understand.

I have already included Rick Rothstein's LookUpConcat UDF along with the Macro as listed below in the attached spreadsheet...
(from

http://www.excelfox.com/forum/f22/lookup-value-and-concatenate-all-found-results-345/

)

UDF + macro

Function LookUpConcat(ByVal SearchString As String, SearchRange As Range, ReturnRange As Range, _
                      Optional Delimiter As String = " ", Optional MatchWhole As Boolean = True, _
                      Optional UniqueOnly As Boolean = False, Optional MatchCase As Boolean = False)
                   
  Dim X As Long, CellVal As String, ReturnVal As String, Result As String
  
  If (SearchRange.Rows.Count > 1 And SearchRange.Columns.Count > 1) Or _
     (ReturnRange.Rows.Count > 1 And ReturnRange.Columns.Count > 1) Then
    LookUpConcat = CVErr(xlErrRef)
  Else
    If Not MatchCase Then SearchString = UCase(SearchString)
    For X = 1 To SearchRange.Count
      If IsError(SearchRange(X)) Then GoTo Continue
      If MatchCase Then
        CellVal = SearchRange(X).Value
      Else
        CellVal = UCase(SearchRange(X).Value)
      End If
      ReturnVal = ReturnRange(X).Value
      If MatchWhole And CellVal = SearchString Then
        If UniqueOnly And InStr(Result & Delimiter, Delimiter & ReturnVal & Delimiter) > 0 Then GoTo Continue
        Result = Result & Delimiter & ReturnVal
      ElseIf Not MatchWhole And CellVal Like "*" & SearchString & "*" Then
        If UniqueOnly And InStr(Result & Delimiter, Delimiter & ReturnVal & Delimiter) > 0 Then GoTo Continue
        Result = Result & Delimiter & ReturnVal
      End If
Continue:
    Next
    
    LookUpConcat = Mid(Result, Len(Delimiter) + 1)
  End If
End Function


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 = "C"
  Const ResultCol As String = "N"
  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

HOWEVER the code will only look up one column at a time (and gives all the result) where as i need it to look say itemA with frequency 1 and give me the desired outcomes as A1,A2 from Column B etc.

Also the macro at the end that uses the UDF LookUpConcat gives an output in every cell, i.e. has repetition that i am trying to eliminate (see Column L for itemB).

Please advise what modification need to be done to the codes ?
thanks in advance,
Shaft.

ps. is it possible to generate 3 sheets automatically everytime the macro is run for corresponding frequency and these sheets to be customised formatted sheets. i.e. i will need to create a customised sheets for each of the frequency and the macro code will dump the results in the corresponding customised sheets?