CONCAT into one cell based on 2 values

[shelbsassy]

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!

[Admin]

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

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

[Rick Rothstein]

   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.

[Admin]

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!