SOLVED

# FILL SERIES BASED ON ABOVE FORMULA

Copper Contributor

# FILL SERIES BASED ON ABOVE FORMULA

Greetings Forum Members,

How do I change from top table to series of list like the one below using formula or anything else?

Currently just copy and paste.

Regards,

MFY92

4 Replies

# Re: FILL SERIES BASED ON ABOVE FORMULA

``=TOCOL(A1:J7,,TRUE)``

With Office 365 or Excel for the web you can apply the TOCOL function.

# Re: FILL SERIES BASED ON ABOVE FORMULA

If you have any alternative way, please let me know.

best response confirmed by MFY92 (Copper Contributor)
Solution

# Re: FILL SERIES BASED ON ABOVE FORMULA

@MFY92 For older versions of Excel, you could try something along these lines:

``=IF(ROW()-ROW(\$A\$8)>ROWS(\$A\$1:\$L\$7)*COLUMNS(\$A\$1:\$L\$7), "< End of data >", INDEX(\$A\$1:\$L\$7, MOD(ROW()-ROW(\$A\$8)-1, ROWS(\$A\$1:\$L\$7))+1, ROUNDUP((ROW()-ROW(\$A\$8))/ROWS(\$A\$1:\$L\$7), 0)))``

Where:

• \$A\$1:\$L\$7 is the data range
• ROWS(\$A\$1:\$L\$7) is the height of the data range
• COLUMNS(\$A\$1:\$L\$7) is the width of the data range
• ROW()-ROW(\$A\$8) is the current output row
• ROWS(\$A\$1:\$L\$7)*COLUMNS(\$A\$1:\$L\$7) is the total number of output rows

In this example, the above formula was entered in cell A9 (the first output row) and dragged/copied down until it returned "< End of data >".

Please see the attached workbook, where I've broken down the calculations into steps (on the Step by Step worksheet)...

EDIT: the single-formula method shown above is an amalgamation of the step-by-step method demonstrated in the attached workbook, but can also be simplified using the IFERROR function as follows:

``=IFERROR(INDEX(\$A\$1:\$L\$7, MOD(ROW()-ROW(\$A\$8)-1, ROWS(\$A\$1:\$L\$7))+1, ROUNDUP((ROW()-ROW(\$A\$8))/ROWS(\$A\$1:\$L\$7), 0)), "< End of data >")``

# Re: FILL SERIES BASED ON ABOVE FORMULA

Lovely, thank you so much for the step by step attachment. Really helpful
1 best response

Accepted Solutions
best response confirmed by MFY92 (Copper Contributor)
Solution

# Re: FILL SERIES BASED ON ABOVE FORMULA

@MFY92 For older versions of Excel, you could try something along these lines:

``=IF(ROW()-ROW(\$A\$8)>ROWS(\$A\$1:\$L\$7)*COLUMNS(\$A\$1:\$L\$7), "< End of data >", INDEX(\$A\$1:\$L\$7, MOD(ROW()-ROW(\$A\$8)-1, ROWS(\$A\$1:\$L\$7))+1, ROUNDUP((ROW()-ROW(\$A\$8))/ROWS(\$A\$1:\$L\$7), 0)))``

Where:

• \$A\$1:\$L\$7 is the data range
• ROWS(\$A\$1:\$L\$7) is the height of the data range
• COLUMNS(\$A\$1:\$L\$7) is the width of the data range
• ROW()-ROW(\$A\$8) is the current output row
• ROWS(\$A\$1:\$L\$7)*COLUMNS(\$A\$1:\$L\$7) is the total number of output rows

In this example, the above formula was entered in cell A9 (the first output row) and dragged/copied down until it returned "< End of data >".

Please see the attached workbook, where I've broken down the calculations into steps (on the Step by Step worksheet)...

EDIT: the single-formula method shown above is an amalgamation of the step-by-step method demonstrated in the attached workbook, but can also be simplified using the IFERROR function as follows:

``=IFERROR(INDEX(\$A\$1:\$L\$7, MOD(ROW()-ROW(\$A\$8)-1, ROWS(\$A\$1:\$L\$7))+1, ROUNDUP((ROW()-ROW(\$A\$8))/ROWS(\$A\$1:\$L\$7), 0)), "< End of data >")``