Filling in a column based on the vale of multiple rows and columns.

[edmonj]

Looking for a way I can fill in a column at the end of my crosstab with target values for the most recent week in the rows that correspond with that target.

I have a data set that originally looks like this:

Week	Thing1	Thing2	Thing3	Thing4	Target1	Target2	Target3	Target4
1	62	148	10	147	351	163	319	32
2	366	37	31	86	360	183	257	296
3	238	70	124	122	333	190	313	189
4	394	141	95	65	194	335	124	51
5	352	72	172	88	60	85	345	56

That I transpose and crosstab to get to this:

Key	Name	1	2	3	4	5	WoW	Target
1	Thing1	62	366	238	394	352	-10.7%
2	Thing2	148	37	70	141	72	-48.9%
3	Thing3	10	31	124	95	172	81.1%
4	Thing4	147	86	122	65	88	35.4%
5	Target1	351	360	333	194	60	-69.1%
6	Target2	163	183	190	335	85	-74.6%
7	Target3	319	257	313	124	345	178.2%
8	Target4	32	296	189	51	56	9.8%

I'm looking for a way to have the target column show the latest week in the case 5 target for each Thing.

So the end result would look like this (I'll have to add a % to target as well but if I can get how to bring the target over this isn't an issue):

Key	Name	1	2	3	4	5	WoW	Target	% to Target
1	Thing1	62	366	238	394	352	-10.7%	60	586.7%
2	Thing2	148	37	70	141	72	-48.9%	85	84.7%
3	Thing3	10	31	124	95	172	81.1%	345	49.9%
4	Thing4	147	86	122	65	88	35.4%	56	157.1%

I've tried a few things but have yet to get anything that works.  I'm open to either adding it in before transposing and cross tab, but I add the keys after the cross tab to order my report render accordingly and thought it would be an easy thing to reference as out of my 59 rows/keys only 12 have targets.

[BS_THE_ANALYST]

@edmonj You're welcome! It was a fun challenge