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?

 

[bartonxiv]

What about this? A unique ID but an aggregate column where values in column C need to be summed if they match.
ID Aggregate Value
109 BP1 100
102 BP1 500
103 BP2 2000
104 BP3 4000
105 BP4 4000
106 BP4 8900
107 BP4 3000
108 BP4 8000

I have tried doing a nested For loop. The first cheks to see if column b items match, then the second adds the values in column C. So far, no soup.

Thank you.

 

[mikerickson]

Have you looked at SUMIF

=SUMIF(B2:B9,"BP1",C2:C9) will return 600

 

[bartonxiv]

Thank you!
This works, however it (the plot), thickens. This is perhaps why a function may be better. I was attempting to do it the way the concatenation solution (JoinAll Function) was shown but could not figure out how to add the cells versus concatenating them .

Using the SUMIF function, (=SUMIF(B2:B9,B2,C2:C9))
if B2 and B3 are equal, I want C2 and C3 summed in both answer cells 2 and 3!. In essence, the first match sums up all the values correctly. I want that value to appear in all like rows where Bn=Bn+i where i is an integer.

The problem encountered with adding three values, it sums three in the first answer cell but the next one adds row two and three only, and the last one just return the last value.

Thank you again.

 

[jbeaucaire]

You just need to add in some absolute referencing to fix that:

=SUMIF($B$2:$B$9,B2,$C$2:$C$9)

Now copy THAT formula down...the only reference that will change now is the B2...B3...B4...

 

[bartonxiv]

Excellent! Thank you!

 

[jud_goh]

Hello, using below, how do i limit it to join only unique values?

thanks!

<TABLE style="WIDTH: 355pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=473 border=0><COLGROUP><COL style="WIDTH: 86pt; mso-width-source: userset; mso-width-alt: 4205" width=115><COL style="WIDTH: 73pt; mso-width-source: userset; mso-width-alt: 3547" width=97><COL style="WIDTH: 71pt; mso-width-source: userset; mso-width-alt: 3474" width=95><COL style="WIDTH: 125pt; mso-width-source: userset; mso-width-alt: 6070" width=166><TBODY><TR style="HEIGHT: 15pt" height=20><TD class=xl65 id=td_post_845088 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 86pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=115 height=20>Opportunity Id</TD><TD class=xl65 id=td_post_1812301 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 73pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=97>Business Units</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 71pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=95>Results</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 125pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: yellow" width=166>Desire Results</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>1-4BV05ZXXX</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">ABC</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">ABC, DEF, ABC</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: yellow">ABC, DEF</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>1-4BV05ZXXX</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">DEF</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"></TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: yellow">ABC, DEF</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>1-4BV05ZXXX</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">ABC</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"></TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: yellow">ABC, DEF</TD></TR></TBODY></TABLE>

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

 

[jbeaucaire]

Use the CONCATIF() function from post #8. It already has an optional 5th parameter for setting the NoDuplicates option.

=CONCATIF(A:A, "*", B:B, ", ", TRUE)

 

[jud_goh]

thank you...

 

[hermes980]

Great approach, but it doesn't respect when a filter is applied to the data. Is there anyway to ensure that it only joins filtered (viewable) data?

 

[nvu1991]

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

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.