-1

I would appreciate help how to solve this problem i have in Excel.

I would like to create 100 unique rows with 5 columns, where columns should contain unique numbers from a predefined list (1, 2, 3, 4, 5, 6, 7). Same number can only be used once in the row.

How can I do this with a formula? Will need to populate similar unique rows with fewer columns available as well at a lager stage so a generic approach would be appreciated.

Please see attached example image.

Hope this question make sense what I want to accomplish. Thanks in advance for your help!

/Chris

example

ashleedawg
  • 20,365
  • 9
  • 72
  • 105
Chris74
  • 1
  • 1
  • What have you tried? What version of Excel? – JvdV Feb 22 '22 at 10:28
  • Welcome! Check out "[ask]" as well as how to create a [mcve]. If you [edit] your question to provide more info, it will be easier for others to help you. Also, why the "[tag:random]" tag? – ashleedawg Feb 22 '22 at 10:37
  • I use MS Office 365 Excel. Haven't tried much as I don't know what to do – Chris74 Feb 22 '22 at 11:29
  • Are your list, number of rows and number of columns fixed? If not, please amend your post accordingly - if you are wanting a more generalised solution, that obviously make a difference as it will entail a more complex set-up which allows for variation in these parameters. – Jos Woolley Feb 22 '22 at 11:46
  • Hi, yes they are fixed – Chris74 Feb 22 '22 at 13:58

1 Answers1

1

This set-up 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))

Jos Woolley
  • 8,564
  • 2
  • 4
  • 9
  • It can be more dynamic by using BASE and INDEX: `=LET(rng,A1:A7,cnt,COUNTA(rng),a,BASE(SEQUENCE(cnt^5,,0),cnt,5),b,MMULT(--ISNUMBER(FIND(SEQUENCE(,cnt),a)),SEQUENCE(cnt,,1,0))=5,INDEX(rng,--MID(INDEX(SORT(CHOOSE(SEQUENCE(,2),FILTER(a,b),RANDARRAY(SUM(N(b)))),2),SEQUENCE(100)),SEQUENCE(,5),1)))` This way the user defines the range of inputs and the rest takes care of itself. The only caveat here is that the range can be larger than the number of inputs but the inputs must be at the top of the range without blanks in the middle of the list. – Scott Craner Feb 22 '22 at 16:12
  • @ScottCraner Nice use of `BASE`! But can you just confirm the limits on *rng*? I get errors unless `A1:A6` are non-empty. – Jos Woolley Feb 22 '22 at 18:45
  • I realized that `cnt,COUNTA(rng)` needs to be `cnt,COUNTA(rng)+1`or it misses the last one. But yes, as long as the blanks are all at the bottom and not interspersed with blanks. Also you need to have at least 5 items since there is 5 columns in the output. We could re-write it to include 5 or the max number of entries if less than 5. – Scott Craner Feb 22 '22 at 19:00
  • see https://imgur.com/a/R85hyPZ i am looking at A1:A7 and only have 5 inputs. – Scott Craner Feb 22 '22 at 19:02
  • 1
    `=LET(rng,A1:A7,cnt,COUNTA(rng)+1,a,BASE(SEQUENCE(cnt^MIN(5,cnt-1),,0),cnt,MIN(5,cnt-1)),b,MMULT(--ISNUMBER(FIND(SEQUENCE(,cnt),a)),SEQUENCE(cnt,,1,0))=MIN(5,cnt-1),INDEX(rng,--MID(INDEX(SORT(CHOOSE(SEQUENCE(,2),FILTER(a,b),RANDARRAY(SUM(N(b)))),2),SEQUENCE(MIN(100,PRODUCT(SEQUENCE(cnt-1))))),SEQUENCE(,MIN(5,cnt-1)),1)))` will change the number of columns output to 5 or the number of inputs which ever is less. But realize that 4 will only return 24 and 3 will only return 6 and 2 will only return 2. – Scott Craner Feb 22 '22 at 19:07
  • But your link has a different formula to the one you posted in your first comment. You are adding 1 to the `COUNTA(rng)` statement. – Jos Woolley Feb 22 '22 at 19:11
  • Yes see the comment before where I stated that I missed that need in the first formula. – Scott Craner Feb 22 '22 at 19:11