Multi-Dimensional concatif coding

Here is another way of doing it...( An array entered formula.. use Ctrl + shift + enter to confirm it.)

Formula:

=JoinC(IF(IF($C$18:$I$18=$B2,INDEX($C$19:$I$383,MATCH(C$1,$B$19:$B$383,0))),$C$17:$I$17),", ")

Here is the JoinC Code with will concatenate the results.

Function JoinC(ByRef x As Variant, ByRef Delim As String) As String
    '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    '           Developed by Vikas Gautam                               '
    '         Forum Expert at ExcelForum.Com                            '
    'For Concatenating Arrays or Ranges having One Row and Many Columns '
    '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    dLen = Len(Delim)
    With Application
        SourceArray = .Transpose(.Transpose(x))
        Delim2 = Delim & Delim
        Temp = Replace(Join(SourceArray, Delim), Delim2, Delim)
        
        Do While InStr(1, Temp, Delim2, 1) > 0
            Temp = Replace(Temp, Delim2, Delim)
        Loop
    End With
    
    If Left(Temp, dLen) = Delim Then Temp = Mid(Temp, dLen + 1, Len(Temp))
    If Right(Temp, dLen) = Delim Then Temp = Left(Temp, Len(Temp) - dLen)
    JoinC = Temp

End Function

You didn't provided the Row and Column No. of your output table below.
So I Assumed like this:-

	B	       C	            D               E
1		   2/1/20XX	       2/2/20Xx	        2/3/20XX
2	Team A			
3	Team B			
4	Team C

Note:- Put the above formula in C2 by using CTRL + SHIFT + ENTER and Drag downwards and Sideways.

Table3.JPG



Here is the link you can find the JoinC Code:-
Concatenating Rows (JoinR) and Columns (JoinC)

Hi Vikas, your function with JoinC comes with an error in value. I did use ctrl Shift Enter to go along with the array data. And when I took out 'INDEX($C$19:$I$30,MATCH(C$1,$B$19:$B$30,0))'.. it results in a reference error. Can you help me out once again?

I recognize the code that I wrote for the ConcatIf function.
It looks like I'll have to write a ConcatIfs function.

It will be easier if I could hard code a delimiter.
While your need is for a comma delimiter, I've seen other coders who need commas in the data. Hmm.....

Upgrading to a ConcatIfs is one of those projects that I've been putting off.
I hope there isn't a rush, since I probably wont have time until the weekend.
If that time scale works for you, great.
If you need it sooner, give it a shot yourself and I can guide you through any problems. If you do it your self, dump the NoDuplicates argument and the If Instr.... line.

Hi WildEye..
I guess, there is a problem with references.
Provide a sample of your workbook, if you can. Then It would a lot easier for me to work upon.

Okay I guess I have been able to achieve it.
Next time, be sure that you provide a sample workbook so that we don't have to create it ourselves.

Here is the revised JoinC code, I have revised it in the post2 as well.

Function JoinC(ByRef x As Variant, ByRef Delim As String) As String
    '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    '           Developed by Vikas Gautam                               '
    '         Forum Expert at ExcelForum.Com                            '
    'For Concatenating Arrays or Ranges having One Row and Many Columns '
    '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    dLen = Len(Delim)
    With Application
        SourceArray = .Transpose(.Transpose(x))
        Delim2 = Delim & Delim
        Temp = Replace(Join(SourceArray, Delim), Delim2, Delim)
        
        Do While InStr(1, Temp, Delim2, 1) > 0
            Temp = Replace(Temp, Delim2, Delim)
        Loop
    End With
    
    If Left(Temp, dLen) = Delim Then Temp = Mid(Temp, dLen + 1, Len(Temp))
    If Right(Temp, dLen) = Delim Then Temp = Left(Temp, Len(Temp) - dLen)
    JoinC = Temp

End Function

Check the attached file:-

Here is the CountIfs UDF that I've developed.
The syntax is =CountIfs(stringsRange, CriteriaRange1, Criteria1, CritereriaRange2, Criteria2,...., [optional Delimiter])

The last argument is and optional delimiter that defaults to space.

Note that any of the ranges can be rectangular, they need not be single column or row ranges.
Note also that if the CriteriaRanges and Criterias are omitted, it will return a delimited string of the values in the StringsRange.

Function ConcatIfs(stringsRange As Range, ParamArray Criterias() As Variant) As String
    Dim Delimiter As String
    Dim Size As Long
    Dim RowCount As Long, ColumnCount As Long
    Dim RowOfInterest As Long, ColumnOfInterest As Long
    Dim i As Long, j As Long, k As Long
    Dim flag As Boolean
    
    Delimiter = " ": Rem default delimiter
    Size = UBound(Criterias)
    If UBound(Criterias) Mod 2 = 0 Then
        Delimiter = Criterias(UBound(Criterias))
        Size = Size - 1
    End If
    
    With stringsRange
        Set stringsRange = Application.Intersect(.Cells, .Parent.UsedRange)
    End With
    RowCount = stringsRange.Rows.Count
    ColumnCount = stringsRange.Columns.Count
    For k = 0 To Size Step 2
        With Criterias(k)
            Set Criterias(k) = Application.Intersect(.Cells, .Parent.UsedRange)
        End With
        
        With Criterias(k)
            If RowCount < .Rows.Count Then RowCount = .Rows.Count
            If ColumnCount < .Columns.Count Then ColumnCount = .Columns.Count
        End With
    Next k
    
    For i = 1 To RowCount
        For j = 1 To ColumnCount
            flag = True
            For k = 0 To Size Step 2
                RowOfInterest = Application.Min(i, Criterias(k).Rows.Count)
                ColumnOfInterest = Application.Min(j, Criterias(k).Columns.Count)
                flag = flag And (WorksheetFunction.CountIf(Criterias(k).Cells(RowOfInterest, ColumnOfInterest), Criterias(k + 1)) = 1)
            Next k
            If flag Then
                RowOfInterest = Application.Min(i, stringsRange.Rows.Count)
                ColumnOfInterest = Application.Min(j, stringsRange.Columns.Count)
                ConcatIfs = ConcatIfs & Delimiter & CStr(stringsRange.Cells(RowOfInterest, ColumnOfInterest).Value)
            End If
        Next j
    Next i
    ConcatIfs = Mid(ConcatIfs, Len(Delimiter) + 1)
End Function

EDIT:
The attached is an example workbook