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, 2022 in Others by Kithuzzz
• 38,000 points
750 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, 2022 by narikkadan
• 63,600 points

Related Questions In Others

0 votes
1 answer

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

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

answered Apr 4, 2023 in Others by Kithuzzz
• 38,000 points
597 views
0 votes
1 answer

Transposing columns to rows in Excel

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

answered Oct 1, 2022 in Others by narikkadan
• 63,600 points
1,188 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, 2022 in Others by narikkadan
• 63,600 points
1,180 views
0 votes
1 answer

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

answered Dec 25, 2022 in Others by narikkadan
• 63,600 points
884 views
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, 2022 in Others by Edureka
• 13,690 points
4,073 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, 2022 in Others by Edureka
• 13,690 points
2,003 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, 2022 in Others by Edureka
• 13,690 points
982 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, 2022 in Others by narikkadan
• 63,600 points
1,799 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, 2022 in Others by narikkadan
• 63,600 points
1,111 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