How to merge the data in a number of cells in a list based on a reference

[Chris Slater]

Dear All,
I have a requirement to link a number of text based cells where "Concatenate" just won't do.
It relates to a list of comments linked together by a reference number. The reference number is in Column A and the Comments are in Column B. The requirement is to try and merge all of the comments for each instance of the reference together into one cell.
For example:
1 The
1 Cat
1 Sat
1 On
1 The
1 Mat
2 The
2 Cow
2 Jumped
2 Over
2 The
2 Moon

Should become
1 The Cat Sat On The Mat
2 The Cow Jumped Over The Moon

The above is a simplified version, the actual list is further complicated by the fact that each comment can have multiple lines - ie Carriage Returns - within the cell.
There are around 1500 rows in the list.

I'm scratching my head with this one so any help gratefully received.

 

[hagia_sofia]

Hello, could you please share with us what version of Excel are you using? The information is missing within your profile.

 

[Chris Slater]

Hello, could you please share with us what version of Excel are you using? The information is missing within your profile.

Yes, it's MS365 so the latest version. Microsoft® Excel® for Microsoft 365 MSO (Version 2409 Build 16.0.18025.20030) 64-bit

 

[hagia_sofia]

Many thanks for the reply. If you already have access to GROUPBY function, maybe this could work for you:

=GROUPBY(A2:A13,B2:B13,LAMBDA(x,TEXTJOIN(" ",,x)),,0)

If you do not have it, maybe something like this?

=LET(
groups,A2:A13,
words,B2:B13,
b,MAP(groups,LAMBDA(x,COUNTIFS(INDEX(groups,1,0):x,x:x))),
c,MAX(b),
d,UNIQUE(groups),
e,SEQUENCE(ROWS(d))&" | "&MAKEARRAY(ROWS(d),c,LAMBDA(r,c,c)),
f,groups&" | "&b,
g,BYROW(XLOOKUP(e,f,words,""),LAMBDA(x,TEXTJOIN(" ",TRUE,x))),
HSTACK(d,g))

 

[Peter_SSs]

it's MS365

Please update your forum profile (click your user name at the top right of the forum, then ‘Account details’) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)


If you do not have GROUPBY, I think this should suffice.

24 10 16.xlsm
	A	B	C	D	E
1
2	1	The		1	The Cat Sat On The Mat
3	1	Cat		2	The Cow Jumped Over The Moon
4	1	Sat
5	1	On
6	1	The
7	1	Mat
8	2	The
9	2	Cow
10	2	Jumped
11	2	Over
12	2	The
13	2	Moon
Groups

Cell Formulas
Range	Formula
D2:E3	D2	=LET(a,A2:A13,u,UNIQUE(a),HSTACK(u,BYROW(u,LAMBDA(r,TEXTJOIN(" ",,IF(a=r,B2:B13,""))))))
Dynamic array formulas.

 

[RobP]

In case you wanted a VBA approach .. this might get what you needed ?

Sub merge()

Dim lr, x, y, i As Long
Dim arr, vals As Variant
Dim answer As String
Dim C As Collection
Dim R As Range

Set C = New Collection

'assumes col A contains the references. if not - change "A" to whatever..
lr = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
y = 1

'load array with all rows of data
arr = Range("A1", "B" & lr).Value
   
   'populate collection with unique values of arr()
    On Error Resume Next
    For Each R In ActiveSheet.Range("A1:A" & lr).Cells
        C.Add R.Value, CStr(R.Value)
    Next
    On Error GoTo 0
       
    'use unique value array refs to concat the text
    For Each vals In C
        For x = LBound(arr) To UBound(arr)
            If vals = arr(x, 1) Then
                answer = answer & Cleanup(ByVal arr(x, 2)) & " "
            End If
        Next x
        Range("C" & y) = answer
        y = y + 1
        answer = ""
    Next vals
End Sub

Public Function Cleanup(ByVal Str As String) As String
    'Remove non-printable characters from a string
    Dim cleanString As String
    Dim i As Integer

    cleanString = Str
    For i = Len(cleanString) To 1 Step -1
        Select Case Asc(Mid(Str, i, 1))
            Case 1 To 31, Is >= 127
               
                cleanString = Left(cleanString, i - 1) & Mid(cleanString, i + 1)

            Case Else
                'string is good
        End Select
    Next i

    Cleanup = cleanString

End Function

 

[RobP]

In case you wanted a VBA approach .. this might get what you needed ?

Sub merge()

Dim lr, x, y, i As Long
Dim arr, vals As Variant
Dim answer As String
Dim C As Collection
Dim R As Range

Set C = New Collection

'assumes col A contains the references. if not - change "A" to whatever..
lr = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
y = 1

'load array with all rows of data
arr = Range("A1", "B" & lr).Value
  
   'populate collection with unique values of arr()
    On Error Resume Next
    For Each R In ActiveSheet.Range("A1:A" & lr).Cells
        C.Add R.Value, CStr(R.Value)
    Next
    On Error GoTo 0
      
    'use unique value array refs to concat the text
    For Each vals In C
        For x = LBound(arr) To UBound(arr)
            If vals = arr(x, 1) Then
                answer = answer & Cleanup(ByVal arr(x, 2)) & " "
            End If
        Next x
        Range("C" & y) = answer
        y = y + 1
        answer = ""
    Next vals
End Sub

Public Function Cleanup(ByVal Str As String) As String
    'Remove non-printable characters from a string
    Dim cleanString As String
    Dim i As Integer

    cleanString = Str
    For i = Len(cleanString) To 1 Step -1
        Select Case Asc(Mid(Str, i, 1))
            Case 1 To 31, Is >= 127
              
                cleanString = Left(cleanString, i - 1) & Mid(cleanString, i + 1)

            Case Else
                'string is good
        End Select
    Next i

    Cleanup = cleanString

End Function

oops, with sample data.. sorry.

Book1
	A	B	C	D	E	F
1	1	The	The Cat Sat On The Mat
2	1	Cat	The Cow Jumped Over The Moon
3	1	Sat	my friend and the dog
4	1	On
5	1	The
6	1	Mat
7	2	The
8	2	Cow
9	2	Jumped
10	2	Over
11	2	The
12	2	Moon
13	3	my friend and
14	3	the
15	3	dog
Sheet1

 

[Peter_SSs]

the actual list is further complicated by the fact that each comment can have multiple lines - ie Carriage Returns - within the cell.

I had overlooked this.
However you have not said, or given examples of, what you want to happen in this circumstance. If the above suggestions do not do what you want, perhaps you could elaborate on this point and give some more realistic sample data and expected results.

 

[Chris Slater]

Thanks for all your input so far guys. I guess I'll have a play and see what results I get. As far as the 'Multiple Lines in a Cell' issue goes, I'm not sure of what is possible really. Can you ask Excel to add carriage returns in a cell? I really don't know if that is possible.

 

[Peter_SSs]

I'm not sure of what is possible really. Can you ask Excel to add carriage returns in a cell? I really don't know if that is possible.

It really depends on what you have and what you are trying to achieve, which is why I said ..

If the above suggestions do not do what you want, perhaps you could elaborate on this point and give some more realistic sample data and expected results.

As far as giving sample data and expected results go I suggest that you investigate XL2BB for providing sample data & expected results to make it easier for helpers to understand just what you have & where it is and also what you want & where it is to be.
(If you have trouble with XL2BB, review the "XL2BB Icons greyed out" link in the 'Known XL2BB issues' section near the top of the XL2BB Instructions page linked above.)

Also, any solution may well depend on your Excel version and you have not acted on my request about that so other readers may miss the information.

Please update your forum profile (click your user name at the top right of the forum, then ‘Account details’) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)