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!

---

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

---

Quote:

---

Originally Posted by Admin

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.

Quote:

---

Originally Posted by Rick Rothstein

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!