Extract a subset from a list

[dsipp]

I have a large linear list with three columns. First column is a date, second is a number and the third is curency. This table is charted to show a linear growth over time. Each week a new row is added to the list. I already have used the OFFSET formula to make the linear chart dynamic. What I am having trouble with is creating a sublist that extracts the data based on the year of the date in column A. VLOOKUP allowed me to get the sublist for the first year only. I am try to dynamically create data sets to chart the data to show comparison year over year. So all dates from 2008 create a series, 2009 creates a series and so on. I have tried MATCH, INDEX and other combinations. Am I making this harder than it is?

 

[jbeaucaire]

You can use a SUMPRODUCT() trick to count how many rows have the same year.

Excel Workbook
	A	B	C	D
1	1/1/2009
2	2/1/2009
3	3/1/2009
4	4/1/2009
5	5/1/2009		2009	12
6	6/1/2009		2010	12
7	7/1/2009		2011	2
8	8/1/2009
9	9/1/2009
10	10/1/2009
11	11/1/2009
12	12/1/2009
13	1/1/2010
14	2/1/2010
15	3/1/2010
16	4/1/2010
17	5/1/2010
18	6/1/2010
19	7/1/2010
20	8/1/2010
21	9/1/2010
22	10/1/2010
23	11/1/2010
24	12/1/2010
25	1/1/2011
26	1/2/2011
Sheet1

Does that help?

 

[dsipp]

Thank you for your reply. Although this is not the reult I am looking for. I want to extract all data from the main list and create a new listing. So if there were 20 rows for the year 2008, then I would get a new table with 20 entries.

Regards,
David

 

[Marcelo Branco]

If your dates are in ascending order in column A of Sheet1, maybe this to year = 2009

Names Manager > New
Name: Year2009
Refers to: =INDEX(Sheet1!$A$1:$A$26,MATCH(2009,YEAR(Sheet1!$A$1:$A$26),0)):INDEX(Sheet1!$A$1:$A$26,MATCH(2009,YEAR(Sheet1!$A$1:$A$26)))

You can create other names to Year 2010, 2011 etc changing the year in the formula
(i used the data provided by jbeaucaire)

M.

 

[Marcelo Branco]

There is a wrong space at the end of the formula

YEAR(Sheet1!$A$1:$A$2 6)))

The correct is

YEAR(Sheet1!$A$1:$A$26)))

M.

 

[dsipp]

Thanks for the reply. How would I go about using the result to build the 2nd list?

 

[Marcelo Branco]

Thanks for the reply. How would I go about using the result to build the 2nd list?

By the 2nd list you mean a list for 2008, 2010 etc?

M.

 

[dsipp]

yes. I want to use the other list as series for graphs.

 

[Marcelo Branco]

yes. I want to use the other list as series for graphs.

Say you need a dynamic range for 2010:

Names Manager > New
Name:Year2010
Refers to:
=INDEX(Sheet1!$A$1:$A$26,MATCH(2010,YEAR(Sheet1!$A$1:$A$26),0)):INDEX(Sheet1!$A$1:$A$26,MATCH(2010,YEAR(Sheet1!$A$1:$A$26)))


To get the data corresponding to year 2010
If, for example, the data corresponding to year 2010 is in column B you can use
Names Manager > New
Name: Data2010
refers to: =OFFSET(Year2010,0,1)

M.

 

[dsipp]

I guess I just do not understand where I would use the formulas you have created?

David