Forum Discussion

Sameer_Kuppanath_Sultan's avatar
Sameer_Kuppanath_Sultan
Brass Contributor
Sep 21, 2020

Numbers to be continued from Column line to row line

Hi All.. Wish you a Great Day...   I have attached a sheet herewith, in which table A represent data entry form, (Data to be entered in column wise on each date) and Table B represent output data w...
excel
Excel for web
SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Sep 21, 2020

Sameer_Kuppanath_Sultan 

That could be like

=FILTER(
  INDEX($C$4:$Q$17,INT(SEQUENCE(14*15,,15)/15),MOD(SEQUENCE(15*14)-1,15)+1),
  INDEX($C$4:$Q$17,INT(SEQUENCE(14*15,,15)/15),MOD(SEQUENCE(15*14)-1,15)+1)
)

 

  • Sameer_Kuppanath_Sultan's avatar
    Sameer_Kuppanath_Sultan
    Brass Contributor
    Sep 22, 2020

    Thanks-SergeiBaklan it works. 

     

    adding to- If I need to extend this result to next column 1-25 and 26-50, what will be the change in the formula??

    • SergeiBaklan's avatar
      SergeiBaklan
      Diamond Contributor
      Sep 22, 2020

      Sameer_Kuppanath_Sultan 

      I added parameters as named ranges / formulas, you may find them in Name Manager

      For another range just edit Range name here, press F2 to edit in bottom bar.

      With that formulas are

      Take No:
=SEQUENCE(Rows*Columns)

Contnues Num
=FILTER(
  INDEX(Range,INT(SEQUENCE(Rows*Columns,,Columns)/Columns),MOD(SEQUENCE(Columns*Rows)-1,Columns)+1),
  INDEX(Range,INT(SEQUENCE(Rows*Columns,,Columns)/Columns),MOD(SEQUENCE(Columns*Rows)-1,Columns)+1)
)

      Thus you don't need to change the formula to another range, only name. Please check in attached file.