I want to make 100 distinct rows with five columns, each containing a different number from a specified list (1, 2, 3, 4, 5, 6, 7). In a row, a number may only be used once.

How can a formula help me with this? At a later stage, comparable unique rows would need to be filled in with fewer columns available as well, therefore a general strategy would be appreciated.

See the illustration provided as an example.

Oct 14, 2022 in Others 571 views

## 1 answer to this question.

This setup isn't readily generalizable, though since you confirmed that the number of list entries, rows, and columns is fixed:

`=LET(α,SEQUENCE(64199,,12345),β,MMULT(0+ISNUMBER(FIND(SEQUENCE(,7),α)),SEQUENCE(7)^0)=5,0+MID(INDEX(SORT(CHOOSE(SEQUENCE(,2),FILTER(α,β),RANDARRAY(SUM(N(β)))),2),SEQUENCE(100)),SEQUENCE(,5),1))`
• 63,700 points

## How to use COUNTIFS with multiple conditions and columns in Excel?

Use  SUMPRODUCT and double unay operators: =SUMPRODUCT ...READ MORE

+1 vote

## In community edition I am unable to create tenant ,Getting this error"the filed client IP Address must be a string or array type with maximum length 64"

length is too high READ MORE

## Create charts in excel sheet [ in same sheet along with data C#]

Unfortunately, it's not that simple. Use the ...READ MORE

## Get random value in the range of plus/minus 10% of a cell value in Excel

Why not just use RANDBETWEEN(B2*0.9, B2*1.1) if ...READ MORE

## Calculate Birthdate from an age using y,m,d in Excel

Hi To Calculate the date, we can ...READ MORE

## Convert Rows to Columns with values in Excel using custom format

1 I having a Excel sheet with 1 ...READ MORE