Transpose-sheet containing a matrix of mixed dates and values.

**a94andwi** · 02-21-2010, 08:01 AM

Hello.

I have a sheet containing a matrix of mixed dates and values.
I am trying to transpose this inte another form of matrix where dates is in columns instead of rows.

I need this to be dynamic.
I've tried functions such as Index, Match and lookup but I can't seem to make it work.

I have attached an excel sheet of how I need it to be transfered.

I hope someone know how to do this.

/Anders

**DonkeyOte** · 02-21-2010, 08:10 AM

If I've understood...

K2: =INDEX($B:$H,COLUMNS($K2:K2)+6*INT((ROWS(K$2:K2)-1)/7),1+MOD(ROWS(K$2:K2)-1,7))
applied across matrix K2:N16

It would of course make more sense to store the ROWS count adjacent to the table so as to avoid repetitive calcs, eg:

J2: =ROWS(J$2:J2)
copied down to J16

then the above is shortened to

K2: =INDEX($B:$H,COLUMNS($K2:K2)+6*INT(($J2-1)/7),1+MOD($J2-1,7))
applied across matrix K2:N16

(change delimiter from comma to semi-colon if required - your locale is not specified)

**martindwilson** · 02-21-2010, 08:10 AM

is it set out just like that?

**a94andwi** · 02-21-2010, 08:47 AM

Hello.

I worked tremendously well. Very happy!
What if I'd want it so be in vertikal mode instead with dates on row 2 and the values on row 3, 4, 5?

Do you have the same quick answer for that question as well?

Thanks
/Anders

**DonkeyOte** · 02-21-2010, 08:53 AM

Well I wouldn't do that because you should generally look to store your data such that you have the largest quantity of data on the vertical axis (ie more rows than columns)

However, purely for sake of demo... let's use a different range for results - ie leave the existing solution as is.

P1: =COLUMNS($P1:P1)
copied across to AC1

P2: =INDEX($B:$H,ROWS($P$2:$P2)+6*INT((P$1-1)/7),1+MOD(P$1-1,7))
copied across matrix P2:AC5

(again change delimiter as required)

**a94andwi** · 02-21-2010, 09:45 AM

Hello.

I need to do a change and add more rows above the data I'm having.
When I do that it doesn't work any longer.

On row 9 my "real" data starts and goes to row 326.

I add my workbook again with your code in it.

/Anders

**DonkeyOte** · 02-21-2010, 10:36 AM

I'm afraid I don't understand - the sample file works and bears no relation to the ranges you specify in your last post.

**a94andwi** · 02-21-2010, 10:41 AM

Hello.

Sorry for my bad english. I'm swedish so sometimes it is not so clear for you I understand.
If I insert rows on row 1 the code does'nt work.
Try and see for yourself. Do you have a clue?

**DonkeyOte** · 02-21-2010, 11:33 AM

Originally Posted by Anders

If I insert rows on row 1 the code doesn't work.

If you're physically inserting / removing rows then yes - as you would expect - the formulas will no longer work.
Equally, if there is no consistent pattern to the "groups" of data (ie rows in each "block") then a formula based approach will be very complex (if viable at all).

I'm not quite sure what you were expecting...