Search code examples
excelrelational-databaselibreoffice-calc

Assign values in large scale into a data base. Formula (libreOffice calc)

I am trying to create a data base in libreOffice spreed-sheet application. And what I need is, the first column to be Id's, but each Id has to fill 100 cells. So I would like to have 2000 Id's and each Id takes up 100 cells, we have 200 000 cells. (Id's values = range(1,2000))

row#1 : row#100 = Id#1 // row#101 : row#200 = Id#2 ....// row#199900 : row#200000 = Id#2000

What I simply want is to assign the value 1 to the first 100 cells in the first column, the value 2 to the next 100 cells in the same column and so on, until I have the 2000 Id's in the first column.

So I would like to find a formula to achieve that with out having to select and scroll manually 2000 times the sheet.

Thanks

Solution

  • If the ID is in A column:

    =QUOTIENT(ROW(A1);100)+1

    The formula adds 1 to integer part of the number of row divided by 100.