Output a comma separated list of inclusive years

**sigmaj** · 07-02-2013, 10:24 AM

I'm new to the forum so bear with me.
Given a list of beginning years and ending years, how can I create a column that gives the whole list of inclusive years separated by a comma?

Start Year End Year List of Years
1985 1995 1985,1986,1987,1988,1989,1990,1991,1992,1993,1994,1995
2000 2009 2000,2001,2002,2003,2004,2005,2006,2007,2008,2009

I'm not that familiar with VB, so if the answer is a VB script, step-by-step instructions would be great.

Thanks so much!
Cheers! sigmaj

**XOR LX** · 07-02-2013, 10:29 AM

Hi,

Not quite sure what you mean by "The goal is to be able to do a VLOOKUP on the List of Years columns" as VLOOKUP on a string will not give you your desired results.

Are you perhaps looking to see, given a particular year, between which set of Start and End Years that year falls between, if any?

Regards

**sigmaj** · 07-02-2013, 10:35 AM

Thanks XOR LX. Sorry about the confusion. I want to be able to output each inclusive year in the cell separated by something, it doesn't necessarily have to be a commma. I already have a search function set up in an existing spreadsheet dashboard that will search for a specific year. So if I type in "1996", the function searches the column for "1996" and return a number. If I type in a wildcard "199*", the function searches and returns the number of all cells that are in the 1990s. Thanks!

**XOR LX** · 07-02-2013, 10:57 AM

Ah, then you're going to require a VBA solution for what you want since Excel's in-built funtions for concatenating strings are woeful when it comes to working with arrays of indeterminate length.

Can I ask why these values can't be stored in separate cells in e.g. separate columns and these columns then be used for the search?

Regards

**XOR LX** · 07-02-2013, 11:04 AM

If it's just a count of the number of times in which a certain year falls within the list of year ranges you have, then you don' t need to concatenate anything.

For example, if your Start Dates and End Dates are in cells A1:A100 and B1:B100 respectively, and the Year for which you want to determine the number of these ranges for which that Year falls between them is in cell D1, then:

=SUMPRODUCT(($A$1:$A$100<=$D$1)*($B$1:$B$100>=$D$1))

Regards