Hi,
Before I describe the formula, let me start at the beginning!! My apologies in advance for this being a "story" post! (I am hoping that you and others would be able to suggest a different way to achieve the desired end result.)
(Due to firewall restrictions that block data storage sites, I am not in a position to upload a sample doc. However, I would be happy to e-mail it to someone if someone would be kind enough to upload the doc. Perhaps Chandoo can enhance this site to accept file attachments!)
Data requirements:
1.I have a list of items that are assigned to groups. However, the group membership can change over time. (For example, itemA could be in GroupA in January, but GroupB in February.)
2.I track the change in group membership using an effective date of when a new group is valid.
3.For every item, I have values per month.
4.Since an item's group membership may change over time, an item's value for a given month is attributed to the group that that item belongs to, in that particular month.
5.Not every Group may have Items
6.An Item may not have a Group assignment in a particular month.
Desired Outcome:
I am attempting to create a summary view by Group for each month.
Here is a sample Item List:
[pre]
Code:
Item Jan-12 Feb-12 Mar-12 Apr-12 May-12 Jun-12 Jul-12
Item1.1 10 10 10 10 10 10 10
Item1.2 10 10 10 10 10 10 10
Item1.3 10 10 10 10 10 10 10
Item2.1 10 10 10 10 10 10 10
Item2.2 10 10 10 10 10 10 10
Item2.3 10 10 10 10 10 10 10
Here is a sample Group list:
[pre][code]Item Group Eff. Date
Item1.1 Group1 1/1/2012
Item1.1 Group4 3/1/2012
Item1.2 Group2 2/1/2012
Item1.3 Group3 3/1/2012
Item1.3 Group4 4/1/2012
Item2.1 Group3 2/1/2012
Item2.2 Group4 1/1/2012
Item2.3 Group5 8/1/2012
Here is the summary view I am attempting to create (where the XX would get replaced by the sum of the values from the Item List, where the Item's group membership for that month matches the Group identified in the respective row):
Groups Jan-12 Feb-12 Mar-12 Apr-12 May-12 Jun-12 Jul-12
Group1 XX XX XX XX XX XX XX
Group2 XX XX XX XX XX XX XX
Group3 XX XX XX XX XX XX XX
Group4 XX XX XX XX XX XX XX
Group5 XX XX XX XX XX XX XX
[/pre]
I used the following formula to get the array in my original post, as an intermediate step to calculate the XX above. (The formula calculates the intermediate array for Group1 and Jan-12.)
(The final step would be to multiply with a month's values from the Item list.
In the formula, the name "GroupData" refers to the Grouplist above.
B2:B7 is the range of items in the Items List above.
=TRANSPOSE(TRANSPOSE(((($B$2:$B$7=TRANSPOSE(INDEX(GroupData,0,1))))))*(INDEX(GroupData,0,3)<=$A$1)) * TRANSPOSE(ROW(A1:INDEX(A:A,ROWS(GroupData))))
The above formula returns the following value for Group1 and Jan-12:
{1,2,0,0,0,0,0,0;0,0,3,0,0,0,0,0;0,0,0,4,5,0,0,0;0,0,0,0,0,6,0,0;0,0,0,0,0,0,7,0;0,0,0,0,0,0,0,0}
This value if displayed in rows and cols, would look as follows, with the Groups from the Group list displayed on top:
Items Group1 Group4 Group2 Group3 Group4 Group3 Group4 Group5
Item1.1 1 2 0 0 0 0 0 0
Item1.2 0 0 3 0 0 0 0 0
Item1.3 0 0 0 4 5 0 0 0
Item2.1 0 0 0 0 0 6 0 0
Item2.2 0 0 0 0 0 0 7 0
Item2.3 0 0 0 0 0 0 0 0[/code][/pre]
For example, Item1.1 has been a member of Group1 and Group4, with Group4 being the most recent membership.
I was planning to somehow turn this into a list such as the following, representing each Item in the ItemList:
{"Group4";"Group2";"Group4";"Group3";"Group4",""}
I was then planning to compare this list to the Group name in the Summary view, to get a boolean array.
I was then planning to multiply the boolean array with the values in the ItemList for that month, and sum them, to get the sum of values for that month.
Obviously, I did not get very far! (By the way, I did not attempt to optimize any of my formulas, since I was trying to get things to work.)
Hope this post makes sense.
Any pointers or suggestions would be very much appreciated.
Thanks,
Sajan.
