Results 1 to 4 of 4

Thread: CONCAT into one cell based on 2 values

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

    Angry CONCAT into one cell based on 2 values

    HTML Code:
    I have a worksheet that I am struggling with.  Basically I have 
    
    Member1      Drug1          Flag1
    Member1      Drug2          Flag1
    Member1      Drug3          Flag1
    Member2      Drug1          Flag1
    Member2      Drug2          Flag2
    Member3      Drug1          Flag1
    Member3      Drug2          Flag2
    
    What I am trying to achieve is If the MemberID=MemberID and FlagID = FlagID then concat all drugs for that member and flag into one cell.
    
    I created a helper column that assigns values using an IF statement to validate if the memberid = memberid and the flagid=flagid then 1 else 1+1
    so now i have a column with 1,1,1,1 for the first member and 2,2,2,2,2 for the second member and basically I want to have the first 3 lines of member 1 blank and concat all the values for the drugs for those four rows into the last cell before the next member/flag switch.
    
    I have looked for hours and haven't been able to find anything better than this:
    
    These are the 4 drugs                      This is the helper column         This is what I am trying to achieve
    BUPROPION    TAB 100MG                1                                                   
    OXYBUTYNIN   TAB 10MG ER            1                                                  
    HYDROCO/APAP TAB 7.5-325            1                                                     
    GABAPENTIN   CAP 100MG                1                                         BUPROPION    TAB 100MG, OXYBUTYNIN   TAB 10MG ER, HYDROCO/APAP TAB 7.5-
                                                                                                      325, GABAPENTIN   CAP 100MG
    
    Is this possible?  Thank you!

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

    Welcome to ExcelFox !!!

    Try this UDF.

    and use like:

    =CONCATIFS(C1:C30,", ",A1:A30,E2,B1:B30,F2)

    where C1:C30 - concatenate range
    ", " - delimiter
    A1:A30 - First Criteria range
    E2 - First criteria
    B1:B30 - second criteria range
    F2 - second criteria

    Code:
    Option Explicit
    
    Function CONCATIFS(ByVal ConcatCol As Variant, ByVal Delim As String, ParamArray ParamA() As Variant) As Variant
        
        '//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 InStr(1, strMatch & "|", "|" & ConcatCol(lngLoopR) & "|", lngCase) = 0 Then
                    lngIndex = lngIndex + 1
                    varOP(lngIndex) = ConcatCol(lngLoopR)
                    strMatch = strMatch & "|" & ConcatCol(lngLoopR)
                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)

  3. #3
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    659
    Rep Power
    13
    Quote Originally Posted by Admin View Post
    Code:
       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
    The following comment is for the red highlighted text, but it also applies (concept-wise) to the blue highlighted text as well. You do not need to use the double Application.Transpose to assign a single row horizontal range of cells as a one-dimensional array to a variable. Here is a more direct way...

    ConcatCol = Application.Index(ConcatCol.Value2, 1, 0)

    Note: The Value or Value2 property must be specified or the code won't work.

  4. #4
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,122
    Rep Power
    10
    Quote Originally Posted by Rick Rothstein View Post
    The following comment is for the red highlighted text, but it also applies (concept-wise) to the blue highlighted text as well. You do not need to use the double Application.Transpose to assign a single row horizontal range of cells as a one-dimensional array to a variable. Here is a more direct way...

    ConcatCol = Application.Index(ConcatCol.Value2, 1, 0)

    Note: The Value or Value2 property must be specified or the code won't work.
    Good point!
    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. Return a comment based on values in another cell
    By msiyab in forum Excel Help
    Replies: 5
    Last Post: 05-23-2015, 02:47 AM
  2. Replies: 3
    Last Post: 11-22-2013, 08:22 AM
  3. IF STATEMENT BASED ON EQUAL VALUES IN RANGE
    By mrmmickle1 in forum Excel Help
    Replies: 3
    Last Post: 09-09-2013, 05:54 PM
  4. Replies: 2
    Last Post: 05-30-2013, 07:28 PM
  5. Offset based on Values in Column E
    By mrmmickle1 in forum Excel Help
    Replies: 7
    Last Post: 12-04-2012, 10:06 AM

Tags for this Thread

Posting Permissions

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