Create unique rows in Excel with limited data to be used in multiple columns

0 votes

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.

example

Oct 14 in Others by Kithuzzz
• 20,660 points
35 views

1 answer to this question.

0 votes

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))
answered Oct 14 by narikkadan
• 37,660 points

Related Questions In Others

0 votes
1 answer

Transposing columns to rows in Excel

Due to the amount of your data, ...READ MORE

answered Oct 1 in Others by narikkadan
• 37,660 points
86 views
0 votes
1 answer

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

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

answered Oct 11 in Others by narikkadan
• 37,660 points
67 views
0 votes
1 answer
0 votes
1 answer

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

Hello, yes u can find your birthdate using ...READ MORE

answered Feb 16 in Others by Edureka
• 13,640 points
145 views
0 votes
1 answer

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

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

answered Feb 16 in Others by Edureka
• 13,640 points
248 views
0 votes
0 answers

Convert Rows to Columns with values in Excel using custom format

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

Feb 17 in Others by Edureka
• 13,640 points
112 views
0 votes
1 answer

IF - ELSE IF - ELSE Structure in Excel

In this case, you can use nested ...READ MORE

answered Feb 18 in Others by gaurav
• 22,040 points
77 views
0 votes
1 answer

Word Mail Merge with Excel data has to be saved in different files with custom names

Try this: Public Sub Mail_Merge() On Error GoTo ErrH Dim ...READ MORE

answered Nov 6 in Others by narikkadan
• 37,660 points
53 views
0 votes
1 answer

How to create a drop-down in excel with custom values

You can accomplish that using code rather ...READ MORE

answered Sep 25 in Others by narikkadan
• 37,660 points
72 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP