CODEWITHSUNDEEP
randomgoogle-sheetsgoogle-sheets-formulaarray-formulasgoogle-sheets-query
lollah mullah
lollah mullah

Reputation: 13

random numbers distributed across cells in Google Sheets

let's say I have 100 cells and I want to distribute numbers 1-100 across those 100 cells with no two cells having the same number.

is there a command in Google Sheets that would do this.

Please note the important things all 100 numbers in the range should be distributed across the 100 cells.

Upvotes: 0

Views: 475

Answers (2)

user11982798
user11982798

Reputation: 1908

Try this:

=arrayformula(row(A1:offset(A1,100-1,0)))

This will give you 1 to 100 in your cells (1 column, 100 row), you can change 100 with other number

If you would like between number you must change 1 to you begin value, for example 50 to 100:

=arrayformula(row(A50:offset(A50,100-50,0)))

Sorry, this not random number, but sequence number

For random number, you can just change A50:A100 (Mean random from 50 to 100) to your range for example A1:A30 (mean random from 1 to 30):

=query(arrayformula({(randbetween( 1,row(A50:A100))),row(A50:A100)}),"Select Col2 order by Col1")

Upvotes: 0

player0
player0

Reputation: 1

use this:

=ARRAYFORMULA(ARRAY_CONSTRAIN(VLOOKUP(QUERY({ROW(A1:A100), 
 RANDBETWEEN(ROW(A1:A100)^0, 9^9)}, "select Col1 order by Col2 asc"), 
 {ROW(A1:A100), ROW(A1:A100)}, 2, 0), 100, 1))

UPDATE:

=ARRAYFORMULA(ARRAY_CONSTRAIN(VLOOKUP(QUERY({ROW(INDIRECT("A1:A"&A1)), 
 RANDBETWEEN(ROW(INDIRECT("A1:A"&A1))^0, 9^9)}, "select Col1 order by Col2 asc"), 
 {ROW(INDIRECT("A1:A"&A1)), ROW(INDIRECT("A1:A"&A1))}, 2, 0), A1, 1))

enter image description here

Upvotes: 1

Related Questions