Range Placement

[Gamermatt]

Hello,

I need a macro or formula for this task. I need lines (products) to be sorted into year categories.

The product years can be found in column AA. The category years can be found in AC - AI.

The product years should be compared to the category years. It should look within each range and decide if the product years belong in the category years. As long as the category years contain at least 1 year that the products years contain, it should be placed in that category (examples below).

If there are no category years, nothing should be done.

If there are more than one category years that it belongs in, the whole row should be copied, and the next category years should reside below it.


Before:

Excel Workbook
	AA	AB	AC	AD	AE	AF	AG	AH	AI
2	2005-2009	*	1000-3000	1988-1991	1992-1995	1996-2000	2001-2003	2004-2005	2006-Up
3	2004-2007	*	1000-3000	2000-2002	2003-2008	*	*	*	*
4	2000-2001	*	1000-3000	2000-2002	2003-2008	*	*	*	*
5	2000-2008	*	1000-3000	*	*	*	*	*	*
6	2003-2008	*	1000-3000	2000-2002	2003-2008	*	*	*	*
7	2005-2009	*	1000-3000	1988-1991	1997-2003	2004-Up	*	*	*
8	1990-1999	*	*	*	*	*	*	*	*
Sheet1

After:

Excel Workbook
	AA	AB	AC	AD	AE	AF	AG	AH	AI
2	2005-2009	*	1000-3000	*	*	*	*	*	*
3	2005-2009	*	2004-2005	*	*	*	*	*	*
4	2005-2009	*	2006-Up	*	*	*	*	*	*
5	2004-2007	*	1000-3000	*	*	*	*	*	*
6	2004-2007	*	2003-2008	*	*	*	*	*	*
7	2000-2001	*	1000-3000	*	*	*	*	*	*
8	2000-2001	*	2000-2002	*	*	*	*	*	*
9	2000-2008	*	1000-3000	*	*	*	*	*	*
10	2003-2008	*	1000-3000	*	*	*	*	*	*
11	2003-2008	*	2003-2008	*	*	*	*	*	*
12	2005-2009	*	1000-3000	*	*	*	*	*	*
13	2005-2009	*	2004-Up	*	*	*	*	*	*
14	1990-1999	*	*	*	*	*	*	*	*
Sheet1

Note: I can replace "-Up" with "-2011" in order to be consistent.


How would I go about doing this?

Thanks in advanced,

Matt

 

[BrandonWLH]

Hmmm.. I think you may need to redo the spreadsheet to make it easier on yourself in the end.

Rather than putting 2000-2009 in 1 cell since that isn't a number, make the range as 3 cells.

AA would be 2000, AB would be the - AC is 2009 and then you turn off gridlines and border those 3 cells, it will look like a single cell but you will have much quicker and easier access to get to the years, then you also will know, the AA column is always the start year and the AC column always the end year. X is greater than AA1 or less than AC1, much easier I feel.