0

I'm trying to get multiple names from different rows into one cell in one row, but group it by a value in another column. I also need the list of names to have line breaks between them, not a comma separated list. I'm not sure if this is even going to be possible. I've got some of the pieces I'll need, like =CONCATENATE(TRANSPOSE(B2:B19)) to get the data into one cell, and char(10) to add the line break, but I haven't been able to put it together to get what I want.

Data is currently like this:

enter image description here

What I want:

enter image description here

Even a VBA solution is ok - though it's not my forte. ;) I need the data like this to use in a Word mail merge.

Also note, that there are several more columns of data in the spreadsheet. I left out for simplicity.

2

I suggest a solution that requires bit of VBA.

In this example sample data is in B2:C10. Leave E1 as heading cell and in E2 put the following formula and press CTRL + SHIFT + ENTER from within the formula bar to create an Array Formula. The formula now shall be enclosed in curly braces to indicate that it's an array formula.

=IFERROR(INDEX($B$2:$B$10, MATCH(0, COUNTIF(E$1:$E1, $B$2:$B$10), 0),1),"") 

Drag this down till you get blanks. This first creates a list of unique values from group at B2:B10. Note that wherever you put this formula, at least one cell above it should be available to be referred to. E1 in this case as formula starts in E2.

We are going to use a Function called TEXTJOIN. However in most of the versions of Excel this is not available. You may have it in case you are using Office 365 version of Excel 2016. If not available use below UDF (User Defined Function) in VBA to replicate the same functionality.

Press ALT + F11 to access VBA Editor. Insert a Module from the Insert Menu. Put the following UDF in it.

Function TEXTJOIN1(delimiter As String, ignore_empty As Boolean, ParamArray cell_ar() As Variant)
For Each cellrng In cell_ar
    For Each cell In cellrng
        If ignore_empty = False Then
            result = result & cell & delimiter
        Else
            If cell <> "" Then
                result = result & cell & delimiter
            End If
        End If
    Next cell
Next cellrng
TEXTJOIN1 = Left(result, Len(result) - Len(delimiter))
End Function

Now back to the Excel Sheet, we shall use this function as a UDF in formula. In F2 put the following formula and press CTRL + SHIFT + ENTER to create an Array Formula.

=IFERROR(TEXTJOIN1(CHAR(10),TRUE,IF($B$2:$B$10=E2,$C$2:$C$10,"")),"")

Drag it down till intended rows. Wait, this creates a list of names by group concatenated by Char(10) but to see the right effect you need to enable Wrap Text on the intended cells. You can manually do it from the Format Cells Option in Excel or you can use this below simple Macro to do it for you. Just specify the Range in the beginning. In this example it's E2:F4.

Press ALT + F11 to access VBA Editor. Insert a Module from the Insert Menu and past the following code into it. This creates a Macro named Format1

Sub Format1()

    Range("E2:F4").Select
    With Selection
        .HorizontalAlignment = xlLeft
        .VerticalAlignment = xlTop
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
End Sub

Back in the Excel sheet Press ALT + F8 to access Macro dialog box and Run Format1.

Test this solution at your end and let me know in case of any issues.

enter image description here

1
  • Thanks! This looks like it'd work as well. We were able to find another solution before I went back and checked for answers.
    – Graeck
    Jan 23 '18 at 19:37
2

After posting, and before seeing the other answer I worked with a coworker and we got it working.

Started by sorting by Group then Name.

Then we added a column that would check both to see if the name had already been added (there are duplicates) and if the group was the same or different. If that name was not added already and it was still the same group it concat'ed the new name onto the list from the cell above, using:

=IF(B2=B1,C1,IF(A2=A1,CONCATENATE(C1,CHAR(10),B2),B2))

In another column, we created a running reverse count of names in the group.

=IF(A2=A1,D1-1,COUNTIF(A:A,A2))

Which got this:

enter image description here

Then we just filtered column D by "1":

enter image description here

0

enter image description here

Private Sub MergeDuplicates()


Dim Rng As Range, xCell As Range
Dim xRows As Integer


xTitleId = "Merge Duplicates"

Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)

Application.ScreenUpdating = False
Application.DisplayAlerts = False

xRows = WorkRng.Rows.count

For Each Rng In WorkRng.Columns
    For i = 1 To xRows - 1
        For j = i + 1 To xRows
            If Rng.Cells(i, 1).Value <> Rng.Cells(j, 1).Value Then
                Exit For
            End If
        Next
        WorkRng.Parent.Range(Rng.Cells(i, 1), Rng.Cells(j - 1, 1)).Merge
        i = j - 1

    Next
Next

Application.DisplayAlerts = True
Application.ScreenUpdating = True

End Sub

N.B. Insert this code as Module and back to sheet to RUN it, select the required data range when INPUT BOX appears and finish with Ok.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.