Convert single cell multiple year & year ranges to individual year columns?

slam

Well-known Member
Joined
Sep 16, 2002
Messages
919
Office Version
  1. 365
  2. 2019
Hi gang!

Sorry about that title if it doesn't explain this well! :)

I have a bunch of years in column G like this 5 row example:

1950–1953
2023
1950, 1956–2019, 2021
1950–1951, 1953–1954, 1956, 1959–1961, 1964, 1966
1990, 1992, 1999

I want to convert these to individual year columns. I've got columns J through CE set up to capture these years starting with 2023, working backward to 1950. I want a Y shown in the respective cell where that year is listed in column G.

It's the ranges that are tricky for me (1950–1953); if it was just a listing of each individual year in the same cell (1950, 1951, 1952, 1953), I think I could do it.

Is there an easy way to do this, or do I have to do it manually? Thanks!
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
Like this?

Book1
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAOAPAQARASATAUAVAWAXAYAZBABBBCBDBEBFBGBHBIBJBKBLBMBNBO
1Ranges
21950–19531950195119521953
320232023
41950, 1956–2019, 2021195019561957195819591960196119621963196419651966196719681969197019711972197319741975197619771978197919801981198219831984198519861987198819891990199119921993199419951996199719981999200020012002200320042005200620072008200920102011201220132014201520162017201820192021
51950–1951, 1953–1954, 1956, 1959–1961, 1964, 19661950195119531954195619591960196119641966
61990, 1992, 1999199019921999
Sheet2


VBA Code:
Sub Extract()
Dim r As Range:         Set r = Range("A2:A" & Range("A" & Rows.Count).End(xlUp).Row)
Dim AR() As Variant:    AR = r.Value2
Dim SD As Object:       Set SD = CreateObject("Scripting.Dictionary")
Dim SP() As String
Dim DP() As String
Dim tmp As String

For Each a In AR
    SP = Split(a, ", ")
    For Each d In SP
        DP = Split(d, "–")
        For i = DP(0) To DP(UBound(DP))
            tmp = tmp & i & ","
        Next i
    Next d
    tmp = Left(tmp, Len(tmp) - 1)
    SD.Add tmp, 1
    tmp = vbNullString
Next a

Set r = r.Offset(, 1)
With r
    .Value = Application.Transpose(SD.Keys)
    .TextToColumns DataType:=xlDelimited, Comma:=True
End With
End Sub
 
Upvote 0
Hi lrobbo314! Thank you so much!

My row 1 is column headers for every single year (J:CE i.e. 2023 back to 1950), then I'm just looking for a Y in each cell in those columns where that year occurs in the respective row in column G.

.... but if you don't want to take another stab at it, I can try out your VBA later today and could probably add a formula to do the final piece.
 
Upvote 0
How about this?

Book1 (version 2).xlsb
GHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAOAPAQARASATAUAVAWAXAYAZBABBBCBDBEBFBGBHBIBJBKBLBMBNBOBPBQBRBSBTBUBVBWBXBYBZCACBCCCDCE
1Ranges20232022202120202019201820172016201520142013201220112010200920082007200620052004200320022001200019991998199719961995199419931992199119901989198819871986198519841983198219811980197919781977197619751974197319721971197019691968196719661965196419631962196119601959195819571956195519541953195219511950
21950–19531953195219511950
320232023
41950, 1956–2019, 2021202120192018201720162015201420132012201120102009200820072006200520042003200220012000199919981997199619951994199319921991199019891988198719861985198419831982198119801979197819771976197519741973197219711970196919681967196619651964196319621961196019591958195719561950
51950–1951, 1953–1954, 1956, 1959–1961, 1964, 19661966196419611960195919561954195319511950
61990, 1992, 1999199919921990
Sheet3


VBA Code:
Sub EXII()
Dim r As Range:         Set r = Range("G2:G" & Range("G" & Rows.Count).End(xlUp).Row)
Dim AR() As Variant:    AR = r.Value2
Dim Res() As Variant:   ReDim Res(1 To UBound(AR), 1 To 74)
Dim SP() As String
Dim DP() As String

For i = 1 To UBound(AR)
    SP = Split(AR(i, 1), ", ")
    For Each d In SP
        DP = Split(d, "–")
        For j = DP(0) To DP(UBound(DP))
            Res(i, 2023 - j + 1) = j
        Next j
    Next d
Next i

Range("J2").Resize(UBound(Res), UBound(Res, 2)).Value = Res

End Sub
 
Upvote 0

Forum statistics

Threads
1,223,887
Messages
6,175,199
Members
452,617
Latest member
Narendra Babu D

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