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?

 

[Rick Rothstein]

I know this is from very long ago but can you add to the code so it work horizontally as well. I think it would require 1 more true/false variable for the function to distinguish between vertical and horizontal.

Assuming you will never have more than 65,500 cells worth of data, you could let the code figure it out on its own...

[table="width: 500"]
[tr]
	[td]Function JoinAll(ByVal BaseValue, ByRef Rng As Range, ByVal Delim As String)
  Dim A As Variant, I As Long
  If Rng.Columns.Count = 2 Then
    A = Rng.Value
  Else
    A = Application.Transpose(Rng.Value)
  End If
  For I = 1 To UBound(A, 1)
    If A(I, 1) = BaseValue Then JoinAll = JoinAll & Delim & A(I, 2)
  Next
  JoinAll = Mid(JoinAll, Len(Delim) + 1)
End Function[/td]
[/tr]
[/table]

 

[nvu1991]

Thank you for the great code.

 

[nvu1991]

Assuming you will never have more than 65,500 cells worth of data, you could let the code figure it out on its own...

[TABLE="width: 500"]
<tbody>[TR]
[TD]Function JoinAll(ByVal BaseValue, ByRef Rng As Range, ByVal Delim As String)
  Dim A As Variant, I As Long
  If Rng.Columns.Count = 2 Then
    A = Rng.Value
  Else
    A = Application.Transpose(Rng.Value)
  End If
  For I = 1 To UBound(A, 1)
    If A(I, 1) = BaseValue Then JoinAll = JoinAll & Delim & A(I, 2)
  Next
  JoinAll = Mid(JoinAll, Len(Delim) + 1)
End Function[/TD]
[/TR]
</tbody>[/TABLE]

Is there a way to make the function also handle criteria like greater, less than or equal to? That would make it far more powerful.

 

[Aladin Akyurek]

Thank you for the great code.

Don't you have the native TEXTJOIN on your system?

 

[nvu1991]

Don't you have the native TEXTJOIN on your system?

It's not really a issue of MY system, rather the systems of all people I work with, inside and outside of my company. And as of now, it seems that the most common office version is 2010. Excel 2010 does not have function TEXTJOIN.

 

[Aladin Akyurek]

It's not really a issue of MY system, rather the systems of all people I work with, inside and outside of my company. And as of now, it seems that the most common office version is 2010. Excel 2010 does not have function TEXTJOIN.

I often use a generic udf, called ACONCAT, to the same effect when TEXTJOIN is not available.

 

[Rick Rothstein]

Is there a way to make the function also handle criteria like greater, less than or equal to? That would make it far more powerful.

Here you go. I added a 4th optional argument... if you omit it, it defaults to the quoted equal sign... if you specify it, there are 5 values you can use (note that they must be quoted as shown)...

"<"
"<="
"="
">"
">="

Of course, the values being compared must be numbers for this to work. Here is the revised code...

[table="width: 500"]
[tr]
	[td]Function JoinAll(ByVal BaseValue, ByRef Rng As Range, ByVal Delim As String, Optional Compare As String = "=")
  Dim A As Variant, I As Long
  If Rng.Columns.Count = 2 Then
    A = Rng.Value
  Else
    A = Application.Transpose(Rng.Value)
  End If
  For I = 1 To UBound(A, 1)
    If Evaluate("""" & A(I, 1) & """" & Compare & """" & BaseValue & """") Then JoinAll = JoinAll & Delim & A(I, 2)
  Next
  JoinAll = Mid(JoinAll, Len(Delim) + 1)
End Function[/td]
[/tr]
[/table]

 

[nvu1991]

Here you go. I added a 4th optional argument... if you omit it, it defaults to the quoted equal sign... if you specify it, there are 5 values you can use (note that they must be quoted as shown)...

"<"
"<="
"="
">"
">="

Of course, the values being compared must be numbers for this to work. Here is the revised code...

[TABLE="width: 500"]
<tbody>[TR]
[TD]Function JoinAll(ByVal BaseValue, ByRef Rng As Range, ByVal Delim As String, Optional Compare As String = "=")
  Dim A As Variant, I As Long
  If Rng.Columns.Count = 2 Then
    A = Rng.Value
  Else
    A = Application.Transpose(Rng.Value)
  End If
  For I = 1 To UBound(A, 1)
    If Evaluate("""" & A(I, 1) & """" & Compare & """" & BaseValue & """") Then JoinAll = JoinAll & Delim & A(I, 2)
  Next
  JoinAll = Mid(JoinAll, Len(Delim) + 1)
End Function[/TD]
[/TR]
</tbody>[/TABLE]

It works perfectly, thank you. Is it possible to include the comparison into the BaseValue variable? That way it's similar to the expression already existed in excel. That's a little bit easier for user to get used to the function.

 

[Rick Rothstein]

It works perfectly, thank you. Is it possible to include the comparison into the BaseValue variable? That way it's similar to the expression already existed in excel. That's a little bit easier for user to get used to the function.

I think this will do what you want. Note that if you omit the comparison symbol from the front of the number, an equal sign will be assumed.

Function JoinAll(ByVal BaseValue As Variant, ByRef Rng As Range, ByVal Delim As String)
  Dim A As Variant, I As Long
  If Rng.Columns.Count = 2 Then
    A = Rng.Value
  Else
    A = Application.Transpose(Rng.Value)
  End If
  If BaseValue Like "[!<=>]*" Then BaseValue = "=" & BaseValue
  For I = 1 To UBound(A, 1)
    If Evaluate(A(I, 1) & BaseValue) Then JoinAll = JoinAll & Delim & A(I, 2)
  Next
  JoinAll = Mid(JoinAll, Len(Delim) + 1)
End Function

 

[nvu1991]

I think this will do what you want. Note that if you omit the comparison symbol from the front of the number, an equal sign will be assumed.

Function JoinAll(ByVal BaseValue As Variant, ByRef Rng As Range, ByVal Delim As String)
  Dim A As Variant, I As Long
  If Rng.Columns.Count = 2 Then
    A = Rng.Value
  Else
    A = Application.Transpose(Rng.Value)
  End If
  If BaseValue Like "[!<=>]*" Then BaseValue = "=" & BaseValue
  For I = 1 To UBound(A, 1)
    If Evaluate(A(I, 1) & BaseValue) Then JoinAll = JoinAll & Delim & A(I, 2)
  Next
  JoinAll = Mid(JoinAll, Len(Delim) + 1)
End Function

It's even more perfect than before. Less variable, easier to read code, easier to implement function. Thank you.