Sep 21 2020 06:02 AM - edited Sep 21 2020 06:17 AM
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 where the data to be arranged in row wise excluding "0" from Table A. (Not necessarily to be in date wise)
Do you guys can help on this with either VBA or Formulas??
Sep 21 2020 06:52 AM
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)
)
Sep 22 2020 05:09 AM
Thanks-@Sergei Baklan 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??
Sep 22 2020 05:27 AM
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.
Sep 22 2020 05:28 AM
Sep 22 2020 05:48 AM
By the way, there is no need to number rows here.