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

 

[Tinbendr]

Sub CombineAllDates()
Dim A As Long
Dim B As Long
Dim Ctr As Long
Dim LastRow As Long
Dim Rng1() As Variant
Dim Rng2() As Variant

    With Sheet2

        LastRow = .Cells(.Rows.Count, 1).End(xlUp).Row

        ReDim Rng1(1 To LastRow, 1 To 9)
        ReDim Rng2(1 To 9 * LastRow, 1 To 1)

        Rng1 = .Range("A1:I" & LastRow).Value

        For A = 1 To 9
            For B = 1 To LastRow
            Ctr = Ctr + 1
                Rng2(Ctr, 1) = Rng1(B, A)
            Next
        Next

    End With

    Sheet4.Range("A1").Resize(9 * LastRow, 1).Value = Rng2

    ActiveWorkbook.Worksheets("Sheet4").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet4").Sort.SortFields.Add Key:=Range("A1"), _
                SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet4").Sort
        .SetRange Range("A1:A" & 9 * LastRow)
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

End Sub

 

[eddiegnz1]

David, you can probably imagine how thankfull I am for your help. I appreciate it greatly. I will definitely try that code. I've never used code in excel so it might take me a bit to figure out even where to paste that code at. I'm guessing it's not as easy as pasting it into a cell.

Meanwhile if anyone knows of a place where I can go to learn where and how to paste that code please let me know. Also, if there's a way to achieve the same thing with formulas or functions that'd be great just in case I can't figure out how to use the above code.

Kind Thanks,
Eddie

 

[xenou]

A good intro to macros/vba is here:
http://www.mcgimpsey.com/excel/modules.html
http://www.mcgimpsey.com/excel/index.html#usingvba

If you don't want to use code I would suggest you explain more what your purpose is - the design sounds very confusing (9 columns of different kinds of dates that must be put in one column). Why are all these dates in need of being mixed up like this? What do you need to do?

 

[Tinbendr]

Here's a test file I setup.

 

[eddiegnz1]

https://www.box.com/s/d9c9ca02313108bb2d82
The above link takes you to an excel file that is an example of what I'm trying to achieve. There are comments in some of the cells to further explain it.

The dates are in separate columns because each column represents a different type of thing that can happen in a loan. The interest rate can change, amount of the payment might change, a payment might be made by the borrower the loan might mature, a due date comes up once every month. All those things are different types of events or different types of dates.
Example:
Column A - this column has all the due dates on it. The user types in the first due date and the formulas fill in the rest of due dates after that down the column
Column B - this column has all the dates on which a payment was made by the borrower. each date is manually entered by the user in a different sheet and pulled into this sheet.
column C - this is the dates on which the interest rate changed. this is pulled in by formulas from another sheet.

All these different dates need to end up in a single column and sorted in ascending order. And next to each date on this new column should be a description of what type of date it is (see the link above).
The reason they need to end up in a single column is so that calculations can be done on the amount of interest due and the output of these calculations need to be in sequential order from oldest date to newest date.

 

[xenou]

Now I'm even more confused. How do you calculate interest without interest rates or amount balances? Why do you have loan information that includes nothing but dates? Edit: never mind, I see the answer - "entered by user on a different sheet" is in B and C.

 

[Tinbendr]

OK, try this.

I did it on a separate page as per your first request.

 

[eddiegnz1]

Xenou, sorry about that. I'm struggling to be clear. The link I provided isn't a fully functional workbook and it is a quick file I created with only an example of the date issue. The actual calculations of interest and other things will be done later. Since calculating interest and other things is the easy part for me, I made the sample file so that it focuses on the date issue. In fact, the actual number of date columns will be 10. And the final workbook will do other things and have several sheets and much more.

Once I can learn how to manipulate a few date columns, I can figure out how to tweak it to suit my exact needs.

Xenou, I appreciate that you've taken the time to help me and evaluate my question. I know you're being patient with my lack of clarity and proper wording of my questions.

Kind Thanks,
Eddie

 

[xenou]

I've never seen an interest schedule that required all these dates in a single column as a pre-requisite to determining the interest - for that you should use a normal amortization schedule. There's a lot of red flags here - what kind of loans are you working with that after 2 years no one knows how much interest has been paid?