Excel 2007 : Transpose and offset

**coop110580** · 02-10-2011, 06:23 PM

Hi,

I have spent days transposing information from multiple tabs into a single tab and a more user friendly format. Now, I am being asked to essentially re-do the entire exercise in order to transpose the data from 2 columns over... This would be extremely tedious, and I am wondering whether there is a way to program a macro for this or to use the offset functionality. Or anything else that would do the trick...

Example is attached.

Thanks for your help.

**DonkeyOte** · 02-11-2011, 09:24 AM

Not 100% sure I understand but:

L4:
=MATCH(L$5,Sheet2!$C$4:$D$4,0)

M5:
=INDEX(Sheet2!$4:$10,COLUMNS($M5:M5),$L$4)
copied across to S5

L4 is simply to avoid repetitive calcs.

If in reality the values being retrieved are always numeric then you can use SUMIF also:

M5:
=SUMIF(Sheet2!$B$4:$B$10,M$4,INDEX(Sheet2!$C$4:$D$10,0,MATCH(LOOKUP(REPT("Z",255),$B5:L5),Sheet2!$C$3:$D$3,0)))
copied to S5

Using the SUMIF method M5:S5 can be simply copied and pasted over C5:I5 to replicate results without need for modification
(L4 calc is not nec. in relation to SUMIF method)
In short the SUMIF approach is pretty flexible

**MarvinP** · 02-11-2011, 10:40 AM

Hi coop and welcome to the forum.

If you structure your data as in the attached you might be able to do more with it. See the attached and two different pivot examples.