Merge Columns in Date Order

%3CLINGO-SUB%20id%3D%22lingo-sub-3299892%22%20slang%3D%22en-US%22%3EMerge%20Columns%20in%20Date%20Order%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3299892%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20looking%20for%20a%20formula%20or%20Macro%20that%20can%20merge%2Fcombine%20two%20columns%20of%20data%20into%20one%20based%20on%20date%20order.%20The%20data%20in%20columns%20B%2C%20C%2C%20E%2C%20and%20F%20would%20be%20manually%20entered%2C%20and%20the%20data%20in%20columns%20H%20and%20I%20would%20be%20output%20by%20a%20formula.%20I%20need%20a%20formula%20to%20create%20the%20%22Combines%20Accounts%22%20table%20from%20the%20%22Account%201%22%20and%20%22Account%202%22%20table%20shown%20below.%20Any%20help%20on%20this%20would%20be%20greatly%20appreciated.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20far%20I%20have%20been%20able%20to%20stack%20the%20lists%20on%20top%20of%20each%20other%2C%20as%20well%20as%20combine%20them%20in%20a%20tiling%20fashion%20(Column%20H%20order%20%3D%20B3%2C%20E3%2C%20B4%2C%20E4%2C%20B5%2C%20E5%2C%20Etc.)%2C%20but%20I%20have%20not%20been%20able%20to%20combine%20them%20when%20dates%20are%20random%20in%20each%20column.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDates%20in%20columns%20B%20and%20E%20will%20always%20be%20in%20ascending%20order%20as%20they%20are%20shown%20below.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22ExcelBear_1-1651432917655.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F368329i4E0813E8942CFAAF%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22ExcelBear_1-1651432917655.png%22%20alt%3D%22ExcelBear_1-1651432917655.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3299892%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3350017%22%20slang%3D%22en-US%22%3ERe%3A%20Merge%20Columns%20in%20Date%20Order%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3350017%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20is%20fantastic!%20Thank%20you%20for%20putting%20this%20macro%20together!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20having%20trouble%20modifying%20the%20macro%20to%20add%20additional%20account%20columns.%20I%20have%20added%20snips%20(also%20attached)%20of%20the%20macro%20I%20got%20working%20for%20the%20first%20two%20accounts%20(%22Ent%20bank%20Account%22%20and%20%22%20Southwest%20Credit%20Card%22)%2C%20although%20I%20would%20like%20to%20add%20the%20additional%20accounts%20as%20well%20(%22UMB%20HSA%20Account%22%20and%20%22Venmo%20Account%22).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECould%20you%20help%20me%20to%20modify%20the%20macro%20to%20accommodate%20for%20more%20account%20columns%3F%20Again%2C%20I%20appreciate%20the%20assistance%20on%20this%2C%20my%20macro%20skills%20are%20quite%20limited%20at%20the%20moment.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22ExcelBear_0-1652179736996.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F370449i88F376EB2F30A004%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22ExcelBear_0-1652179736996.png%22%20alt%3D%22ExcelBear_0-1652179736996.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22ExcelBear_1-1652179800468.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F370451i3FAA93DF787CA3C3%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22ExcelBear_1-1652179800468.png%22%20alt%3D%22ExcelBear_1-1652179800468.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3299898%22%20slang%3D%22en-US%22%3ERe%3A%20Merge%20Columns%20in%20Date%20Order%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3299898%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1378288%22%20target%3D%22_blank%22%3E%40ExcelBear%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EHere%20is%20a%20macro%3A%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-visual-basic%22%3E%3CCODE%3ESub%20Combine()%0A%20%20%20%20Dim%20m%20As%20Long%0A%20%20%20%20Application.ScreenUpdating%20%3D%20False%0A%20%20%20%20Range(%22H3%3AH%22%20%26amp%3B%20Rows.Count).Clear%0A%20%20%20%20m%20%3D%20Range(%22B%22%20%26amp%3B%20Rows.Count).End(xlUp).Row%0A%20%20%20%20If%20m%20%26gt%3B%202%20Then%0A%20%20%20%20%20%20%20%20Range(%22B3%3AC%22%20%26amp%3B%20m).Copy%20Destination%3A%3DRange(%22H3%22)%0A%20%20%20%20End%20If%0A%20%20%20%20m%20%3D%20Range(%22E%22%20%26amp%3B%20Rows.Count).End(xlUp).Row%0A%20%20%20%20If%20m%20%26gt%3B%202%20Then%0A%20%20%20%20%20%20%20%20Range(%22E3%3AF%22%20%26amp%3B%20m).Copy%20Destination%3A%3DRange(%22H%22%20%26amp%3B%20m%20%2B%201)%0A%20%20%20%20End%20If%0A%20%20%20%20Range(%22H2%22).CurrentRegion.Sort%20Key1%3A%3DRange(%22H2%22)%2C%20Header%3A%3DxlYes%0A%20%20%20%20Application.ScreenUpdating%20%3D%20True%0AEnd%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3350230%22%20slang%3D%22en-US%22%3ERe%3A%20Merge%20Columns%20in%20Date%20Order%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3350230%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1378288%22%20target%3D%22_blank%22%3E%40ExcelBear%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EHere%20is%20a%20new%20version.%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-visual-basic%22%3E%3CCODE%3ESub%20Combine()%0A%20%20%20%20Dim%20v%20As%20Variant%0A%20%20%20%20Dim%20m%20As%20Long%0A%20%20%20%20Dim%20r%20As%20Long%0A%20%20%20%20Application.ScreenUpdating%20%3D%20False%0A%20%20%20%20'%20Clear%20target%20range%0A%20%20%20%20Range(%22Z4%3AAD%22%20%26amp%3B%20Rows.Count).Clear%0A%20%20%20%20'%20Initialize%20target%20row%0A%20%20%20%20r%20%3D%204%0A%20%20%20%20'%20Change%20and%20expand%20as%20needed%0A%20%20%20%20For%20Each%20v%20In%20Array(%22B%22%2C%20%22H%22%2C%20%22N%22%2C%20%22T%22)%0A%20%20%20%20%20%20%20%20'%20Last%20used%20row%20in%20column%0A%20%20%20%20%20%20%20%20m%20%3D%20Cells(Rows.Count%2C%20v).End(xlUp).Row%0A%20%20%20%20%20%20%20%20If%20m%20%26gt%3B%203%20Then%0A%20%20%20%20%20%20%20%20%20%20%20%20'%20Copy%20data%0A%20%20%20%20%20%20%20%20%20%20%20%20Range(Cells(4%2C%20v)%2C%20Cells(m%2C%20v)).Resize(%2C%205).Copy%20Destination%3A%3DRange(%22Z%22%20%26amp%3B%20r)%0A%20%20%20%20%20%20%20%20%20%20%20%20'%20Increment%20target%20row%0A%20%20%20%20%20%20%20%20%20%20%20%20r%20%3D%20r%20%2B%20m%20-%203%0A%20%20%20%20%20%20%20%20End%20If%0A%20%20%20%20Next%20v%0A%20%20%20%20'%20Sort%20on%20date%20column%0A%20%20%20%20Range(%22Z3%3AAD%22%20%26amp%3B%20r%20-%201).Sort%20Key1%3A%3DRange(%22Z3%22)%2C%20Header%3A%3DxlYes%0A%20%20%20%20Application.ScreenUpdating%20%3D%20True%0AEnd%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3377326%22%20slang%3D%22en-US%22%3ERe%3A%20Merge%20Columns%20in%20Date%20Order%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3377326%22%20slang%3D%22en-US%22%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%3CBR%20%2F%3E%3CBR%20%2F%3EThis%20works%20like%20a%20champ%2C%20thank%20you%20again!%3CBR%20%2F%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E
New Contributor

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.

 

ExcelBear_1-1651432917655.png

 

4 Replies

@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

@Hans Vogelaar 

 

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.

 

ExcelBear_0-1652179736996.png

 

ExcelBear_1-1652179800468.png

 

 

@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
@Hans Vogelaar

This works like a champ, thank you again!