Concatenate Multiple Cells with Array Formula

[mgirvin]

Dear Smartest Excelers In The World,

If I have the following data set in cells A1:C9:

ID Comment First Unique
109876 Low Oil TRUE
109876 Checked On 12/12 FALSE
109877 Checked on 12/15 TRUE
109878 Correct Level TRUE
109877 Correct Level FALSE
109878 Perfect FALSE
109877 New FALSE
109878 Correct Level FALSE

In the First Unique column (column C) I have formulas. In C2 I have this formula (and then I copied it down):

=COUNTIF(A$2:A2,A2)=1

In cell E2, I have this formula to count unique occurrences:

=SUMPRODUCT(C2:C9*1)

In cell G2 I have this formula to extract unique records (entered with Ctrl + Shift + Enter):

=IF(ROWS(G$2:G2)<=E$2,INDEX(A$2:A$9,SMALL(IF(C$2:C$9=TRUE,ROW(C$2:C$9)-ROW(C$2)+1),ROWS(G$2:G2))),"")

Now, in cell H2 I would like a formula that would concatenate all the comments from the Comments column in the data set for each unique ID.

For example, For ID # 109876, I would like the formula to yield this text string in cell H2:

“Low Oil, Checked On 12/12”

Any ideas?

[pgc01]

Hi Mike

You cannot do it with a native formula. You have to use vba. You can write your own UDF or use someone else's.

For ex., if you use the MOREFUNC add-in, you can use the MCONCAT() UDF.

http://www.mrexcel.com/forum/showthread.php?t=126629

[mgirvin]

Dear pgc01,

Thank you. That is good information.

Does the term "Native" mean one cell?

[pgc01]

Does the term "Native" mean one cell?

No, it means the functions available in excel when you install it, like SUM(), VLOOKUP(), AVERAGE(), etc.

You can then add the functions you write, you call them UDFs (Used Defined Functions).

In this case you can write, for ex., a function that concatenates an array.

[jbeaucaire]

This function was posted here recently (Jindon?) and does what you want, I believe:

Function JoinAll(ByVal BaseValue, ByRef rng As Range, ByVal delim As String)
Dim a, i As Long
a = rng.Value
For i = 1 To UBound(a, 1)
    If a(i, 1) = BaseValue Then JoinAll = JoinAll & _
        IIf(JoinAll = "", "", delim) & a(i, 2)
Next
End Function

Used like:
=JoinAll(Cell,Range,Delimiter)

Here it is in action:
Sheet2

	A	B	C	D
22	109876	Low Oil		Low Oil, Checked On 12/12
23	109876	Checked On 12/12		Low Oil, Checked On 12/12
24	109877	Checked On 12/15		Checked On 12/15, Correct Level, New
25	109878	Correct Level		Correct Level, Perfect, Correct Level
26	109877	Correct Level		Checked On 12/15, Correct Level, New
27	109878	Perfect		Correct Level, Perfect, Correct Level
28	109877	New		Checked On 12/15, Correct Level, New
29	109878	Correct Level		Correct Level, Perfect, Correct Level

Spreadsheet Formulas

Cell Formula D22 =joinall(A22,$A$22:$B$29,", ") D23 =joinall(A23,$A$22:$B$29,", ") D24 =joinall(A24,$A$22:$B$29,", ") D25 =joinall(A25,$A$22:$B$29,", ") D26 =joinall(A26,$A$22:$B$29,", ") D27 =joinall(A27,$A$22:$B$29,", ") D28 =joinall(A28,$A$22:$B$29,", ") D29 =joinall(A29,$A$22:$B$29,", ")

Excel tables to the web >> Excel Jeanie HTML 4

[mgirvin]

Dear jbeaucaire,

Wow!

That user defined function by Jindon is so efficient- so perfect for this task!

Blows away the formula approach that I was attempting.

Thanks!

[jbeaucaire]

Definitely. I looked up and confirmed the authorship and added it permanently to the code itself, appropriate, don't you think?

Function JoinAll(ByVal BaseValue, ByRef rng As Range, ByVal delim As String)
'code by Jindon, MrExcel.com MVP
Dim a, i As Long
a = rng.Value
For i = 1 To UBound(a, 1)
    If a(i, 1) = BaseValue Then JoinAll = JoinAll & _
        IIf(JoinAll = "", "", delim) & a(i, 2)
Next
End Function

Jindon

[mikerickson]

The arguments of the UDF ConcatIf mirror those of SumIf, with the addtition of an optional Delimiter argument. For your situation the formula would be
=ConcatIf(A:A, 109876, B:B, ", ")

Function ConcatIf(ByVal compareRange As Range, ByVal xCriteria As Variant, Optional ByVal stringsRange As Range, _
                            Optional Delimiter As String, Optional NoDuplicates As Boolean) As String
    Dim i As Long, j As Long
    With compareRange.Parent
    Set compareRange = Application.Intersect(compareRange, Range(.UsedRange, .Range("a1")))
     End With
    If compareRange Is Nothing Then Exit Function
    If stringsRange Is Nothing Then Set stringsRange = compareRange
    Set stringsRange = compareRange.Offset(stringsRange.Row - compareRange.Row, _
                                                stringsRange.Column - compareRange.Column)
    
        For i = 1 To compareRange.Rows.Count
            For j = 1 To compareRange.Columns.Count
                If (Application.CountIf(compareRange.Cells(i, j), xCriteria) = 1) Then
                    If InStr(ConcatIf, Delimiter & CStr(stringsRange.Cells(i, j))) <> 0 Imp Not (NoDuplicates) Then
                        ConcatIf = ConcatIf & Delimiter & CStr(stringsRange.Cells(i, j))
                    End If
                End If
            Next j
        Next i
        ConcatIf = mid(ConcatIf, Len(Delimiter) + 1)
    End Function

[Yogi Anand]

The following solution uses MCONCAT function from MoreFunc Add-in as mentioned by PgC01 earlier ...

Sheet7

*	A	B	C	D	E	F
2	109876	Low Oil	TRUE	*	109876	Low Oil,Checked On 12/12
3	109876	Checked On 12/12	FALSE	*	*	*
4	109877	Checked on 12/15	TRUE	*	*	*
5	109878	Correct Level	TRUE	*	*	*
6	109877	Correct Level	FALSE	*	*	*
7	109878	Perfect	FALSE	*	*	*
8	109877	New	FALSE	*	*	*
9	109878	Correct Level	FALSE	*	*	*

Spreadsheet Formulas

Cell Formula F2 {=SUBSTITUTE(MCONCAT(IF(A2:A9=E2,B2:B9),","),",FALSE","")}

Excel tables to the web >> Excel Jeanie HTML 4

[mgirvin]

Dear mikerickson and Yogi,

Wow, so many great solutions! Thanks!

And, yes, jbeaucaire, your extra code is appropriate. However, when I pasted mine, I put:

‘Jindon with help from jbeaucaire

You all are great!