Excel 2016 transposing data in rows and columns

I have a data sheet of some 10 columns and 200 rows with data values in the inter-connecting cells.

I need to transpose the rows into columnar data in order to create a journal entry to post into another application. The best way to describe it is to look at the file attached. Table A is an example of how my data is currently laid out and Table B is how I would like to data to be transposed. I've been going round in circles looking for a simple way to achieve this, trying to avoid VBA macros if possible.

Anyone come across this before or know of a solution to the problem?

You can use the formulae below in the cells stated:

A9: =INDEX($B$2:$F$2,MOD(ROWS($1:1)-1,5)+1)

B9: =INDEX($B$3:$F$6,INT((ROWS($1:1)-1)/5)+1,MOD(ROWS($1:1)-1,5)+1)

C9: =INDEX($G$3:$G$6,INT((ROWS($1:1)-1)/5)+1)

Then copy down as required.

You will need to adjust the ranges to suit your real data and change the 5 to 10.

Hope this helps.

Pete

Thanks all for the feedback. I've tried all the suggestion but keep getting an error message.

FlameRetired, yours was the last that I tried but each time I enter the formal I get an Excel error message. I'm not sure if it's because of the version of Excel. Originally I was on Excel 2016 but on checking it says Excel version 16.11 for Office 365.

Anyway, I've attached screenshots of the error messages for the equations

Thanks for the support.

Your regional settings might be set to use a comma as a decimal point, in which case you will need to use a semicolon ( ; ) instead of any commas in the above formulae.

Also, in the second of your screenshots, you have two consecutive commas in the formula you are trying to enter.

Hope this helps.

Pete

Thanks Pete_UK! Worked perfectly...I would have been here a long time trying to figure out that the problem was the decimal settings. Thanks very much.

How difficult would it be to modify the equations to not transpose a line if the intersecting value was zero?