Combine and Sort An Array of Dates

eddiegnz1

New Member
• 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

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
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
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Sheet4").Sort
.SetRange Range("A1:A" & 9 * LastRow)
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

End Sub``````

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

Here's a test file I setup.

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.

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:
OK, try this.

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

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

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?

Replies
2
Views
24
Replies
0
Views
36
Replies
15
Views
247
Replies
7
Views
313
Replies
10
Views
250

1,196,309
Messages
6,014,589
Members
441,828
Latest member
cofracr

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.

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

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