Help needed: aligning dates in multiple colums.

Question

I'm doing an event study on stock returns. I have created a dataset with stock returns, but because they trade on different exchanges the dates don't always match. Is it possible to align the dates that do match together with the data in the column on the left of the dates? So that each row shows the same date (with corresponding stock price) for each company?&nbsp;

sergeibaklan · Answer

tanmanflanjan&nbsp;
I do not have add-in to play with actual data, thus on such model

DD and Value are arrays returned by formula. Result on the right could be generated by
=LET(d, D6:D22, stockdates, E6#, stockvalues, F6#,
  k, SEQUENCE(ROWS(d)),
  values, XLOOKUP(INDEX(d,k), INDEX(stockdates, k), stockvalues,,-1),
 IF({1,0}, d, values))
&nbsp;Instead of ranges for stockdates and stockvalues you may use your formula. And, in general, there is no need to output dates for each stockvalue.

hansvogelaar · Answer

tanmanflanjan&nbsp;
If you don't have Microsoft 365:
It is currently impossible to insert rows since each column contains a single array formula. Therefore, the following macro replaces the formulas with their values, then inserts row.
Sub MatchDates()
    Dim r As Long
    Dim m As Long
    Dim c As Long
    Dim n As Long
    Application.ScreenUpdating = False
    m = Cells(Rows.Count, 1).End(xlUp).Row
    n = Cells(1, Columns.Count).End(xlToLeft).Column
    With Cells(1, 1).Resize(m, n)
        .Value = .Value
    End With
    For c = 2 To n - 1 Step 2
        For r = 2 To m
            If Cells(r, c).Value &gt; Cells(r, 1).Value Then
                Cells(r, c).Resize(1, 2).Insert Shift:=xlShiftDown
            End If
        Next r
    Next c
    Application.ScreenUpdating = True
End Sub

Forum Discussion

Help needed: aligning dates in multiple colums.

2 Replies

Resources