Excel - Concatenate Cells Between Two Values

user_13814867 0
Viewed 77

I'm trying to get all the years in between (and including) two cells indicating the start year and the end year

Such that if 2018 is the start and 2022 is the year, I'm wanting to return 2018 2019 2020 2021 2022

Likewise if 2015 was the start and 2017 was the end, return 2015 2016 2017

What would be the best way to go about this?

enter image description here

excel-formula excel
3 Answers

Using TEXTJOIN and SEQUENCE:

=TEXTJOIN(" ",,SEQUENCE(C5-C4+1,,C4))

enter image description here

user_92458530

Assuming you are passing this along to other users I would add a check to make sure the dates are entered in the correct order.

=IF(C5>=C4,TEXTJOIN(" ",,SEQUENCE(C5-C4+1,,C4)),"Enter the start date in the first cell")
user_119538470

Wasn't familiar with textjoin so I wrote a macro.

Sub years()

Dim beginyr As Integer
Dim endyr As Integer
Dim diff As Integer
Dim addyr As Integer
Dim arrYears(99) As String

With ActiveSheet

    beginyr = .Cells(3, 1)
    endyr = .Cells(4, 1)
    diff = endyr - beginyr
    addyr = 0
    
    For i = 0 To diff
        arrYears(i) = beginyr + addyr
        addyr = addyr + 1
    Next
    
    c = 2
    For Each e In arrYears()
        .Cells(5, c) = e
        c = c + 1
    Next

End With

End Sub
user_49710650
Related