Multiple Criteria Splitout in Excel

Question

I have data that looks like:

A   B   C   D   E   F   G   H   I

1 Fruit Year Jan Feb Mar Apr May Jun Jul

2 Apples 2011 1 0 0 0 0 0 0

3 Apples 2010 0 11 12 11 11 12 11

4 Oranges 2011 11 10 12 10 11 11 10

5 Oranges 2012 5 5 0 0 0 0 0

I need the data in a different format so that I can see the trend as time progresses (ie from Feb 2010 to Jan 2011 for Apples, and Jan 2011 to Jan 2012 for Oranges - some of the data is missing here because I do not have enough rep points to post pictures). This is only a very small sample of the data I have, so I don't want to manually make a new table where each month has the correct year for the value. I understand that I will have 12 new lines for each Fruit/year (because there are 12 months) and this will be OK if there is a formula that can do this automatically. It would be ideal if once the formula is done with one row it will jump to the next. Does anyone have any ideas on how to accomplish this? Would this be easier in Access?

It sounds like you're looking to Unpivot .. check out stackoverflow.com/questions/32115219/… — CLR
You could use something like so (my data is in k1:x5) =SUM(OFFSET(INDIRECT(ADDRESS(MAX(IF((($K$2:$K$5="orange")*($L$2:$L$5=2010)),ROW($K$2:$K$5))-1),MATCH("Mar",$M$1:$X$1,0)+COLUMN(L1))),0,0,1,(12-(MATCH(2010,$L$2:$L$5,0)+ROW(L1))))) this will get from the start month to year end, the formula will need to be changed for the 2nd part, this will be the same, cell 1 of the row, to the cell with the end month. I'll add a little later when I have time. — Nathan_Sav

Nathan_Sav Nathan_Sav · Accepted Answer · 2017-03-27T14:21:33

You would use this for the month-YTD, using orange & mar-2010 to feb-2011 with data in k1:x5

=SUM(OFFSET(INDIRECT(ADDRESS(MAX(IF((($K$2:$K$5="orange")*($L$2:$L$5=2010)),ROW($K$2:$K$5))),MATCH("Mar",$M$1:$X$1,0)+COLUMN($L$1))),0,0,1,(12-(MATCH(2010,$L$2:$L$5,0)+ROW($L$1)))))

and this for Next year jan to month

=SUM(OFFSET(INDIRECT(ADDRESS(MAX(IF((($K$2:$K$5="orange")*($L$2:$L$5=2010+1)),ROW($K$2:$K$5))),COLUMN($M$1))),0,0,1,MATCH("Mar",$M$1:$X$1,0)-1))

Giving a combined formula of

=SUM(OFFSET(INDIRECT(ADDRESS(MAX(IF((($K$2:$K$5="orange")*($L$2:$L$5=2010)),ROW($K$2:$K$5))),MATCH("Mar",$M$1:$X$1,0)+COLUMN($L$1))),0,0,1,(12-(MATCH(2010,$L$2:$L$5,0)+ROW($L$1)))))+SUM(OFFSET(INDIRECT(ADDRESS(MAX(IF((($K$2:$K$5="orange")*($L$2:$L$5=2010+1)),ROW($K$2:$K$5))),COLUMN($M$1))),0,0,1,MATCH("Mar",$M$1:$X$1,0)-1))

Multiple Criteria Splitout in Excel

1 Answers