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.