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

[slam]

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!

 

[lrobbo314]

Like this?

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT	AU	AV	AW	AX	AY	AZ	BA	BB	BC	BD	BE	BF	BG	BH	BI	BJ	BK	BL	BM	BN	BO
1	Ranges
2	1950–1953	1950	1951	1952	1953
3	2023	2023
4	1950, 1956–2019, 2021	1950	1956	1957	1958	1959	1960	1961	1962	1963	1964	1965	1966	1967	1968	1969	1970	1971	1972	1973	1974	1975	1976	1977	1978	1979	1980	1981	1982	1983	1984	1985	1986	1987	1988	1989	1990	1991	1992	1993	1994	1995	1996	1997	1998	1999	2000	2001	2002	2003	2004	2005	2006	2007	2008	2009	2010	2011	2012	2013	2014	2015	2016	2017	2018	2019	2021
5	1950–1951, 1953–1954, 1956, 1959–1961, 1964, 1966	1950	1951	1953	1954	1956	1959	1960	1961	1964	1966
6	1990, 1992, 1999	1990	1992	1999
Sheet2

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

 

[slam]

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.

 

[lrobbo314]

How about this?

Book1 (version 2).xlsb
	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT	AU	AV	AW	AX	AY	AZ	BA	BB	BC	BD	BE	BF	BG	BH	BI	BJ	BK	BL	BM	BN	BO	BP	BQ	BR	BS	BT	BU	BV	BW	BX	BY	BZ	CA	CB	CC	CD	CE
1	Ranges			2023	2022	2021	2020	2019	2018	2017	2016	2015	2014	2013	2012	2011	2010	2009	2008	2007	2006	2005	2004	2003	2002	2001	2000	1999	1998	1997	1996	1995	1994	1993	1992	1991	1990	1989	1988	1987	1986	1985	1984	1983	1982	1981	1980	1979	1978	1977	1976	1975	1974	1973	1972	1971	1970	1969	1968	1967	1966	1965	1964	1963	1962	1961	1960	1959	1958	1957	1956	1955	1954	1953	1952	1951	1950
2	1950–1953																																																																									1953	1952	1951	1950
3	2023			2023
4	1950, 1956–2019, 2021					2021		2019	2018	2017	2016	2015	2014	2013	2012	2011	2010	2009	2008	2007	2006	2005	2004	2003	2002	2001	2000	1999	1998	1997	1996	1995	1994	1993	1992	1991	1990	1989	1988	1987	1986	1985	1984	1983	1982	1981	1980	1979	1978	1977	1976	1975	1974	1973	1972	1971	1970	1969	1968	1967	1966	1965	1964	1963	1962	1961	1960	1959	1958	1957	1956						1950
5	1950–1951, 1953–1954, 1956, 1959–1961, 1964, 1966																																																												1966		1964			1961	1960	1959			1956		1954	1953		1951	1950
6	1990, 1992, 1999																											1999							1992		1990
Sheet3

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