@wiscy
Press Alt+F11 to activate the Visual Basic Editor.
Select Insert > Module.
Copy the following code into the module:
Function ConcatenateIfs(ConcatenateRange As Range, ParamArray Criteria() As Variant) As Variant
' Source: EileensLounge.com, August 2014
Dim i As Long
Dim c As Long
Dim n As Long
Dim f As Boolean
Dim Separator As String
Dim strResult As String
On Error GoTo ErrHandler
n = UBound(Criteria)
If n < 2 Then
' Too few arguments
GoTo ErrHandler
End If
If n Mod 2 = 0 Then
' Separator specified explicitly
Separator = Criteria(n)
Else
' Use default separator
Separator = ","
End If
' Loop through the cells of the concatenate range
For i = 1 To ConcatenateRange.Count
' Start by assuming that we have a match
f = True
' Loop through the conditions
For c = 0 To n - 1 Step 2
' Does cell in criteria range match the condition?
If Criteria(c).Cells(i).Value <> Criteria(c + 1) Then
' If not, we don't have a match
f = False
Exit For
End If
Next c
' Were all criteria satisfied?
If f Then
' If so, add separator and value to result
strResult = strResult & Separator & ConcatenateRange.Cells(i).Value
End If
Next i
If strResult <> "" Then
' Remove first separator
strResult = Mid(strResult, Len(Separator) + 1)
End If
ConcatenateIfs = strResult
Exit Function
ErrHandler:
ConcatenateIfs = CVErr(xlErrValue)
End Function
Switch back to Excel.
Enter the following formula in C2:
=ConcatenateIfs('Data 2'!$A$2:$A$71,'Data 2'!$B$2:$B$71,A2,'Data 2'!$C$2:$C$71,B2, ", ")
Fill or copy down.
Save the workbook as a macro-enabled workbook (.xlsm).
Make sure that you allow macros when you open the workbook.
See the attached version.
