Forum Discussion

ExcelBear's avatar
ExcelBear
Copper Contributor
May 01, 2022

Merge Columns in Date Order

I am looking for a formula or Macro that can merge/combine two columns of data into one based on date order. The data in columns B, C, E, and F would be manually entered, and the data in columns H and I would be output by a formula. I need a formula to create the "Combines Accounts" table from the "Account 1" and "Account 2" table shown below. Any help on this would be greatly appreciated.

 

So far I have been able to stack the lists on top of each other, as well as combine them in a tiling fashion (Column H order = B3, E3, B4, E4, B5, E5, Etc.), but I have not been able to combine them when dates are random in each column.

 

Dates in columns B and E will always be in ascending order as they are shown below.

 

 

excel
Formulas and Functions

4 Replies

  • HansVogelaar's avatar
    HansVogelaar
    MVP
    May 01, 2022

    ExcelBear 

    Here is a macro:

    Sub Combine()
    Dim m As Long
    Application.ScreenUpdating = False
    Range("H3:H" & Rows.Count).Clear
    m = Range("B" & Rows.Count).End(xlUp).Row
    If m > 2 Then
        Range("B3:C" & m).Copy Destination:=Range("H3")
    End If
    m = Range("E" & Rows.Count).End(xlUp).Row
    If m > 2 Then
        Range("E3:F" & m).Copy Destination:=Range("H" & m + 1)
    End If
    Range("H2").CurrentRegion.Sort Key1:=Range("H2"), Header:=xlYes
    Application.ScreenUpdating = True
End Sub
    • ExcelBear's avatar
      ExcelBear
      Copper Contributor
      May 10, 2022

      HansVogelaar 

       

      This is fantastic! Thank you for putting this macro together!

       

      I am having trouble modifying the macro to add additional account columns. I have added snips (also attached) of the macro I got working for the first two accounts ("Ent bank Account" and " Southwest Credit Card"), although I would like to add the additional accounts as well ("UMB HSA Account" and "Venmo Account").

       

      Could you help me to modify the macro to accommodate for more account columns? Again, I appreciate the assistance on this, my macro skills are quite limited at the moment.

       

       

       

       

      • HansVogelaar's avatar
        HansVogelaar
        MVP
        May 10, 2022

        ExcelBear 

        Here is a new version.

        Sub Combine()
    Dim v As Variant
    Dim m As Long
    Dim r As Long
    Application.ScreenUpdating = False
    ' Clear target range
    Range("Z4:AD" & Rows.Count).Clear
    ' Initialize target row
    r = 4
    ' Change and expand as needed
    For Each v In Array("B", "H", "N", "T")
        ' Last used row in column
        m = Cells(Rows.Count, v).End(xlUp).Row
        If m > 3 Then
            ' Copy data
            Range(Cells(4, v), Cells(m, v)).Resize(, 5).Copy Destination:=Range("Z" & r)
            ' Increment target row
            r = r + m - 3
        End If
    Next v
    ' Sort on date column
    Range("Z3:AD" & r - 1).Sort Key1:=Range("Z3"), Header:=xlYes
    Application.ScreenUpdating = True
End Sub

Resources