The formulas that you have tried will only merge the columns to one list in order, to split them as you need takes a little more manipulation.
I'll assume that you have a named range called 'MyData' already created, if not, you will need to create one or edit the formula so that it referes to the actual range where the data is located.
For clarity, the range that MyData refers to is the upper table, A1:E10.
The references to A14 in the formula should all point to the cell where you enter the first formula before dragging it around to fill the rest of the table. Please give special attention to the positions of the $ symbols, these parts of the formula are used as counters, an incorrectly placed $ symbol will cause incorrect counts and incorrect results in the table.
To better understand the formula, I would advise using the evaluation tool on the Formulas tab of the Excel ribbon.
This partworks as a row counter, as you fill down it compares the number of rows with the formula to the number of rows in the source data. If you check the help file for the information on the MOD function, you will see that MOD(10,10) will return 0 (no remainder), while this is correct, for the formula to work correctly, it needs to be 10, the adjustments of -1 before the operation and +1 after give the result needed.
The next part of the formulaworks in a similar way by using a count of the rows to determine the correct column. This time, the formula is using decimals instead of post division remainder, (1/10, 2/10, 3/10, etc) The result is then truncated and multiplied to step right as needed.
Bookmarks