Merge or concatenate column by adjacent cell value

Question

Is it possible to concatenate cells in a column dependent on values (ID) in another column, and then output as a string (possibly in another sheet as it would be cleaner)?

E.g.

ID          Text
1234        a
1234        b
1234        c
4321        a
4321        b
4321        c
4321        d

Output:

1234        a b c
4321        a b c d

Issues:

Column IDs aren't in order (but can be sorted).
Different amounts of each ID

This seemed like a possible VBA solution from How to merge rows in a column into one cell in excel?

Function JoinXL(arr As Variant, Optional delimiter As String = " ")
    'arr must be a one-dimensional array.
    JoinXL = Join(arr, delimiter)
End Function

Example usage:

=JoinXL(TRANSPOSE(A1:A4)," ")

So I thought maybe if INDEX and MATCH etc could be used in conjuction with TRANSPOSE it could work. Just not sure how to go about it.

I can have a column of the unique IDs in another sheet.

Office 365 Excel has a new formula called TextJoin. You can use this in an array form to get what you want. — Scott Craner, Oct 17 '16 at 23:03
@ScottCraner Just looked it up now, looks like it would work. Unfortunately though, this needs to be working in Excel 2010. — Berry, Oct 18 '16 at 00:09

score 1 · Answer 1 · answered Oct 18 '16 at 02:59

While no convenient function like your cited example, consider using a dictionary of collections with the ID column as the key. Below macro assumes data begins at A2 (column headers in first row) with result outputting in D and E columns:

Sub TransposeValuesByID()
    Dim i As Integer, lastrow As Integer
    Dim valDict As Object
    Dim innerColl As New Collection
    Dim k As Variant, v As Variant

    Set valDict = CreateObject("Scripting.Dictionary")

    lastrow = Cells(Rows.Count, "A").End(xlUp).Row
    For i = 2 To lastrow
        If Range("A" & i) = Range("A" & i + 1) Then
            innerColl.Add Range("B" & i)
        Else
            innerColl.Add Range("B" & i)
            valDict.Add CStr(Range("A" & i).Value), innerColl
            Set innerColl = Nothing
        End If
    Next i

    i = 2
    For Each k In valDict.keys
        Range("D" & i) = k
        For Each v In valDict(k)
            Range("E" & i) = Trim(Range("E" & i) & " " & v)
        Next v
        i = i + 1
    Next k    
End Sub

score 1 · Answer 2 · answered Oct 18 '16 at 13:09

Since all you want is space between you can use your code with a couple changes.

If your data is Vertical you need to transpose the array to make it a one dimensional array:

Function JoinXL(arr As Variant, Optional delimiter As String = " ")
    'arr must be a one-dimensional array.
    arr = Application.Transpose(arr)

    JoinXL = Join(arr, delimiter)
End Function

If it is horizontal then use what you have.

The main change is how you call it.

Use the following array formula:

=TRIM(JoinXL(IF($A$2:$A$8=C2,$B$2:$B$8,"")," "))

being an array it needs to be confirmed with Ctrl-Shift-Enter instead of Enter when exiting edit mode. If done correctly then Excel will put {} around the formula.

The If passes an array of values or blanks depending on if the cell is equal to the criteria.

Put this in the first cell Hit Ctrl-Shift-Enter. Then drag/copy down

score 1 · Accepted Answer · answered Jan 18 '17 at 21:59

This solution is nice because it works even in cases where:

The text you're concatenating contains spaces.
You want to use a delimiter other than a space (ie a comma).

First, add "Transpose" to your custom function

Function JoinXL(arr As Variant, Optional delimiter As String = " ")
'arr must be a one-dimensional array.

arr = Application.Transpose(arr)
JoinXL = Join(arr, delimiter)

End Function

For vertical arrays, use this formula (replace comma+space with your delimiter of choice, and "{}" with your garbage characters of choice):

{=SUBSTITUTE(SUBSTITUTE(JoinXL(IF($A$2:$A$31=D3,$B$2:$B$31,"{}"),", "),"{}, ",""),", {}", "")}

For horizontal arrays, use this formula:

{=SUBSTITUTE(SUBSTITUTE(JoinXL(IF(TRANSPOSE($E$19:$AH$19)=D12,TRANSPOSE($E$20:$AH$20),"{}"),", "),"{}, ",""),", {}", "")}

Make sure you enter the formulas as array formulas (Ctrl+Shift+Enter after typing formula in cell).

score 0 · Answer 4 · answered Oct 18 '16 at 04:39

I've come up with a work around, that although a little cumbersome works quite nicely.

Table has to be sorted by ID.

In another sheet. ID: (Column A)

1234

MIN REF: (Column B)

=ADDRESS(MATCH(A2,OCCRANGE,0)+1,6)

MAX REF: (Column C)

=ADDRESS(MATCH(A2,OCCRANGE)+1,6)

RANGE: (Column D)

=CONCATENATE("'OCCS COMBINED'!",B2,":",C2)

STRING: (Column E)

{=IF([@[MIN REF]]=[@[MAX REF]],INDIRECT(CONCATENATE("'OCCS COMBINED'!",[@[MIN REF]])),JoinXL(TRANSPOSE(INDIRECT(D2)), " "))}

Merge or concatenate column by adjacent cell value

4 Answers4