Results 1 to 10 of 10

Thread: How search two values and concatenate the result from one column !!!

  1. #1
    Junior Member
    Join Date
    Dec 2017
    Posts
    6
    Rep Power
    0

    How search two values and concatenate the result from one column ?

    I have tried to implement the VBA below (I found it in this forum) in the attached excel file, but does not work with two search criteria !!! Please, some help.

    Code:
    Function Multi_LookUpConcat(ByVal SearchList As String, SearchRange As Range, ReturnRange As Range, _
                          Optional SearchListDelimiter As String = ",", _
                          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
      
    'Parse the SearchList into Strings
    ' Spaces next to the delimiters will be ignored
    Dim SearchString As String
    Dim List As String
    Dim C1 As Integer
    Dim C2 As Integer
    
      If StrComp(SearchList, "") = 0 Then
        Multi_LookUpConcat = ""
    
      ElseIf (SearchRange.Rows.Count > 1 And SearchRange.Columns.Count > 1) Or _
         (ReturnRange.Rows.Count > 1 And ReturnRange.Columns.Count > 1) Then
        Multi_LookUpConcat = CVErr(xlErrRef)
      
      Else
        
        SearchList = SearchList & SearchListDelimiter   'Ensure that it runs at least once
        C1 = 1
        C2 = InStr(C1, SearchList, SearchListDelimiter)
        
        While C2 > 0
            SearchString = Trim(Mid(SearchList, C1, C2 - C1))
    
            If Not MatchCase Then SearchString = UCase(SearchString)
            For X = 1 To SearchRange.Count
              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
       
        ' Advance the pointers to search for the next element
        C1 = C2 + 1
        C2 = InStr(C1, SearchList, SearchListDelimiter)
        Wend
        
        Multi_LookUpConcat = Mid(Result, Len(Delimiter) + 1)
      
      End If
      
    End Function
    Attached Files Attached Files
    Last edited by pub; 12-21-2017 at 04:06 PM.

  2. #2
    Senior Member
    Join Date
    Jun 2012
    Posts
    328
    Rep Power
    6
    I don't like exclamation marks.

  3. #3
    Junior Member
    Join Date
    Dec 2017
    Posts
    6
    Rep Power
    0
    I replaced "!!!" with "?". Now, can you give me a solution to my issue ?

  4. #4
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,106
    Rep Power
    10
    Hi

    You could try this UDF.

    Use it as

    =CONCATIFS($C$2:$C$50,", ",$A$2:$A$50,F$1)

    or

    =CONCATIFS($C$2:$C$50,", ",$A$2:$A$50,F$1,$B$2:$B$50,$E2)


    Code:
    Option Explicit
    
    Function CONCATIFS(ByVal ConcatCol As Variant, ByVal Delim As String, ParamArray ParamA() As Variant) As String
        
        '//ParamA=each pair should be Criteria range followed by it's criteria
        
        Dim lngLoopC        As Long
        Dim lngLoopR        As Long
        Dim lngLoop         As Long
        Dim lngCount        As Long
        Dim lngCounter      As Long
        Dim lngIndex        As Long
        Dim lngCase         As Long
        Dim varOP()         As Variant
        Dim strMatch        As String
        Dim blnTranspose    As Boolean
        
        If TypeOf ConcatCol Is Range Then
            If ConcatCol.Columns.Count > 1 And ConcatCol.Rows.Count = 1 Then
                blnTranspose = True
                ConcatCol = Application.Transpose(Application.Transpose(ConcatCol.Value2))
            ElseIf ConcatCol.Columns.Count = 1 And ConcatCol.Rows.Count > 1 Then
                ConcatCol = Application.Transpose(ConcatCol.Value2)
            End If
        End If
        
        For lngLoop = LBound(ParamA) To UBound(ParamA)
            If TypeOf ParamA(lngLoop) Is Range Then
                If blnTranspose Then
                    ParamA(lngLoop) = Application.Transpose(Application.Transpose(ParamA(lngLoop).Value2))
                Else
                    ParamA(lngLoop) = Application.Transpose(ParamA(lngLoop).Value2)
                End If
            End If
        Next
        
        ReDim varOP(1 To UBound(ConcatCol))
        lngCount = (1 + UBound(ParamA)) \ 2
        For lngLoopR = LBound(ConcatCol) To UBound(ConcatCol)
            lngCounter = 0
            For lngLoopC = LBound(ParamA) To UBound(ParamA) Step 2
                If LCase(ParamA(lngLoopC)(lngLoopR)) = LCase(ParamA(lngLoopC + 1)) Then
                    lngCounter = lngCounter + 1
                End If
            Next
            If lngCount = lngCounter Then
                If Len(Trim(ConcatCol(lngLoopR))) Then
                    If InStr(1, strMatch & "|", "|" & ConcatCol(lngLoopR) & "|", lngCase) = 0 Then
                        lngIndex = lngIndex + 1
                        varOP(lngIndex) = ConcatCol(lngLoopR)
                        strMatch = strMatch & "|" & ConcatCol(lngLoopR)
                    End If
                End If
            End If
        Next
        If lngIndex Then
            ReDim Preserve varOP(1 To lngIndex)
            CONCATIFS = Join(varOP, Delim)
        End If
        
    End Function
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

  5. #5
    Junior Member
    Join Date
    Dec 2017
    Posts
    6
    Rep Power
    0
    Thanks for the reply but it does not work.
    I updated the existing module using the VBA code above but when I try to use the two recommended formulas, it displays: #NAME?
    So, what's the reason for it being displayed:#NAME?

  6. #6
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,106
    Rep Power
    10
    Where did you put the code? The code should go into a standard module.
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

  7. #7
    Junior Member
    Join Date
    Dec 2017
    Posts
    6
    Rep Power
    0
    I put the code in a VBA module. How can I check if it's a standard module?

  8. #8
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,106
    Rep Power
    10
    In the VBE window, Go to Insert > Module
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

  9. #9
    Junior Member
    Join Date
    Dec 2017
    Posts
    6
    Rep Power
    0
    Thanks. Now it is working.

    There would still be a last problem where I would like to use CONCATIFS (see the attached file below).
    So, there are three columns in this file: Motivation, Names and A + B. Since Column B is filled randomly, depending on the B values, column C will be automatically populated. In the example from the file, now in cell F2, I would like to list (separated by ";") only the content of C2, C3 and C4 cells.
    Attached Files Attached Files

  10. #10
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,106
    Rep Power
    10
    In D2 and copied down,

    =LEN(B2)>0

    Now use;

    =CONCATIFS(C2:C11,";",D2:D11,TRUE)
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

Similar Threads

  1. Replies: 2
    Last Post: 12-21-2017, 06:28 PM
  2. Replies: 2
    Last Post: 07-23-2013, 07:24 PM
  3. Replies: 24
    Last Post: 06-17-2013, 11:13 AM
  4. Function to Concatenate Values
    By Admin in forum Download Center
    Replies: 1
    Last Post: 12-13-2012, 12:08 PM
  5. Concatenate multiple values
    By mcpizzle in forum Excel Help
    Replies: 3
    Last Post: 08-30-2012, 04:33 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •