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?

 

[Excel_basic]

sorry mgirvin

I missed the posting date.

 

[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

Is it possible to make this function also handle text? I don't know how to identify the BaseValue variable type in VBA. For now, my solution is using 2 different functions, 1 for text, another one for numbers.

 

[Rick Rothstein]

Is it possible to make this function also handle text? I don't know how to identify the BaseValue variable type in VBA. For now, my solution is using 2 different functions, 1 for text, another one for numbers.

I do not remember this all that clearly... can you give me a small sample of values and the result you want to see for them?

 

[nvu1991]

Here is the result I want to see. You have provided the codes that works for both case 1,2 and 3,4. The code works for case 1,2 do not work for case 3,4 and vice versa. If it's possible I would like to have one function that work for both text and number like

1,2,3,4. Sorry to put a link, I couldn't find a way to upload a file or image.

https://ibb.co/c45xQe