Forum Discussion

Matt_Paz's avatar
Matt_Paz
Copper Contributor
Feb 25, 2025
Solved

Combining two columns in a table into one ordered list

Hi all Hope you are well! I know how to use VSTACK in order to get two columns of data into one column. However, my needs are a little more bespoke. How would I solve this below? Thank you!
Formulas and Functions
  • OliverScheurich's avatar
    OliverScheurich
    Feb 26, 2025
    =DROP(REDUCE("",UNIQUE(A1:A14),LAMBDA(u,v,VSTACK(u,v,FILTER(B1:B14,A1:A14=v)))),1)

    If you work with the latest version of Excel you can use this formula.

JKPieterse's avatar
JKPieterse
Silver Contributor
Feb 26, 2025

Nice challenge for a lambda function, but too complicated for me. Here is a VBA user-defined function that returns what you need:

Function StackTableBy1stColumn(proj, detail)
    Dim i As Long
    Dim res As String
    Dim projData As Variant
    Dim detData As Variant
    projData = proj.Value
    detData = detail.Value
    For i = LBound(projData, 1) To UBound(projData, 1)
        If i = 1 Then
            res = projData(1, 1) & vbNewLine & detData(1, 1)
        ElseIf projData(i, 1) <> projData(i - 1, 1) Then
            res = res & vbNewLine & projData(i, 1) & vbNewLine & detData(i, 1)
        Else
            res = res & vbNewLine & detData(i, 1)

        End If
    Next
    StackTableBy1stColumn = Application.Transpose(Split(res, vbNewLine))
End Function

 

Resources