Combine and Sort An Array of Dates

[eddiegnz1]

• I have nine columns of dates
• all columns are on the same sheet and right next to each other (e.g. A through I)
• Each column is not sorted because each date is derived through either a formula or a Vlookup, etc
• Each column represents a different set of dates...with each column being a different type of date than all the others. e.g. one column is all the payment due dates, another column might be the dates when the payment was actually received, another column is the date on which the interest rate changed, etc

I need to combine all these dates into one column and then sort that new column so that they are ascending from top to bottom (i.e. the top date is the oldest date and the bottom date is the newest). The new column needs to end up on a different sheet.

Kind Thanks,
Eddie

 

[eddiegnz1]

delete/retype

 

[eddiegnz1]

Tinbendr / David
Thank you much for that file. It's amazing what you can do with VBA and it gives me hope because it very nearly does what I need. There are two things that are notable.

The sorting is a little off in that it sorts each section separately and groups them by type of event. Below is an example of what I mean;

Example of the wrong way to sort:
01-15-2010 payment received
02-15-2010 payment received
03-17-2010 payment received
01-14-2010 Interest rate changed
02-12-2010 Interest rate changed
03-16-2010 Interest rate changed

Example of the right way to sort:
01-14-2010 Interest rate changed
01-15-2010 payment received
02-12-2010 Interest rate changed
02-15-2010 payment received
03-16-2010 Interest rate changed
03-17-2010 payment received

So in otherwords, what I seek is a sequential chronoligical account of every event mixed together. Not grouped by type of event.

Also, I only need column A and column B in the final output on Sheet 2. In other words, I only need the date and the description of the type of event. I have to go in and create formulas for the other columns. I only included them in the sample file to provide an example.

Thank you millions !
Eddie

 

[Tinbendr]

Hmm, it sorted correctly on the first column for me, but it really needed a sort on the second column, too.
This also does NOT put the formulas in Col C & D.

If you don't want the subtotal either, just Comment out the last three-four lines of the sub.

Sub CombineAllDates()
Dim A As Long
Dim B As Long
Dim LastRow As Long
Dim DestLR As Long

    DestLR = Sheet2.Cells(Sheet2.Rows.Count, "B").End(xlUp).Row + 1
    Sheet2.Range("A3:D" & DestLR).ClearContents
    Sheet2.Range("A3:D" & DestLR).Font.Bold = False
    
    With Sheet1 'Change to source sheet.

    For A = 1 To 8
        LastRow = .Cells(.Rows.Count, A).End(xlUp).Row
        For B = 2 To LastRow
            DestLR = Sheet2.Cells(Sheet2.Rows.Count, "A").End(xlUp).Row + 1
            Sheet2.Cells(DestLR, "A").Value = .Cells(B, A).Value
            Sheet2.Cells(DestLR, "B").Value = .Cells(1, A).Value
            'Sheet2.Cells(DestLR, "C").Formula = "=RC[-2]-R[-1]C[-2]"
            'Sheet2.Cells(DestLR, "D").Formula = "=rc[-1]*4.55"
        Next
    Next
    
    End With

    ActiveWorkbook.Worksheets("Sheet2").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet2").Sort.SortFields.Add Key:=Range("A3:A" & DestLR) _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("Sheet2").Sort.SortFields.Add Key:=Range("B3:B" & DestLR) _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet2").Sort
        .SetRange Range("A3:B" & DestLR)
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

    Sheet2.Range("B" & DestLR + 2) = "Total Amount of Interest"
    Sheet2.Range("D" & DestLR + 2).Formula = "=sum(D4:D" & DestLR & ")"
    Sheet2.Range("B" & DestLR + 2, "D" & DestLR + 2).Font.Bold = True
    
    'Sheet2.Range("C3:D3").ClearContents

End Sub