Numbers to be continued from Column line to row line

%3CLINGO-SUB%20id%3D%22lingo-sub-1691395%22%20slang%3D%22en-US%22%3ENumbers%20to%20be%20continued%20from%20Column%20line%20to%20row%20line%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1691395%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20All..%20Wish%20you%20a%20Great%20Day...%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20attached%20a%20sheet%20herewith%2C%20in%20which%20table%20A%20represent%20data%20entry%20form%2C%20(Data%20to%20be%20entered%20in%20column%20wise%20on%20each%20date)%20and%20Table%20B%20represent%20output%20data%20where%20the%20data%20to%20be%20arranged%20in%20row%20wise%20excluding%20%220%22%20from%20Table%20A.%20(Not%20necessarily%20to%20be%20in%20date%20wise)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDo%20you%20guys%20can%20help%20on%20this%20with%20either%20VBA%20or%20Formulas%3F%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1691395%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20for%20web%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Highlighted
Contributor

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??

 

 

 

5 Replies
Highlighted

@Sameer_Kuppanath_Sulta 

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)
)

 

Highlighted

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??

Highlighted

@Sameer_Kuppanath_Sulta 

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

image.png

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.

 

Highlighted

Automatically number rows
Excel for Microsoft 365 Excel 2019 Excel 2016 Excel 2013 Excel 2010 Excel 2007
Unlike other Microsoft Office programs, Excel does not provide a button to number data automatically. But, you can easily add sequential numbers to rows of data by dragging the fill handle to fill a column with a series of numbers or by using the ROW function.

Tip: If you are looking for a more advanced auto-numbering system for your data, and Access is installed on your computer, you can import the Excel data to an Access database. In an Access database, you can create a field that automatically generates a unique number when you enter a new record in a table.

What do you want to do?
Fill a column with a series of numbers

Use the ROW function to number rows

Display or hide the fill handle

Fill a column with a series of numbers
Select the first cell in the range that you want to fill.

Type the starting value for the series.

Type a value in the next cell to establish a pattern.

Tip: For example, if you want the series 1, 2, 3, 4, 5..., type 1 and 2 in the first two cells. If you want the series 2, 4, 6, 8..., type 2 and 4.

Select the cells that contain the starting values.

Note: In Excel 2013 and later, the Quick Analysis button is displayed by default when you select more than one cell containing data. You can ignore the button to complete this procedure.

Drag the fill handle Fill handle across the range that you want to fill.

Note: As you drag the fill handle across each cell, Excel displays a preview of the value. If you want a different pattern, drag the fill handle by holding down the right-click button, and then choose a pattern.

To fill in increasing order, drag down or to the right. To fill in decreasing order, drag up or to the left.
Highlighted

@Lewis-H 

By the way, there is no need to number rows here.