Combine and Sort An Array of Dates

eddiegnz1

New Member
Joined
Jun 5, 2012
Messages
40
  • 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
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
Code:
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
 
Upvote 0
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
 
Upvote 0
Upvote 0
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.
 
Upvote 0
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.
 
Last edited:
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,954
Members
448,535
Latest member
alrossman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top