Formula to create a list of table entries by row

Question

I need a formula that takes my table of 5 columns and an arbitray number of rows and creates a list row by row in a single column like the following example. I appreciate your assistance.

Answer 1

Hi Guy,

Good day to you and thank you for querying in this forum. Whenever you have any problem, feel free to post and community members will be happy to help.

Regards,

Christophe

Answer 2

Hi,

In cell C3, enter this formula

=IFERROR(INDEX($A$3:$A$19,SEQUENCE(ROUNDUP(COUNTA($A$3:$A$19)/5,0),5)),"")

Hope this helps.

There is a way to solve this in the Query Editor as well. Let me know if you want to solve the problem with that method as well.

Answer 3

Hi,

Try this formula in cell A3:

=INDEX(C3:G5,INT(SEQUENCE(ROWS(C3:G5)*COLUMNS(C3:G5),,0,1)/COUNTA(C3:G3))+1,MOD(SEQUENCE(ROWS(C3:G5)*COLUMNS(C3:G5),,0,1),COUNTA(C3:G3))+1)

This formula works if you have installed Office 365 for Windows, otherwise you can use this below formula:

=IFERROR(INDEX($C$3:$G$5,INT((ROW(A1)-1)/COLUMNS($C$3:$G$5))+1,MOD(ROW(A1)-1,COLUMNS($C$3:$G$5))+1),"")

HTH