'Create unique rows in Excel with limited data to be used in multiple columns
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
Solution 1:[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))
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|---|
| Solution 1 | Jos Woolley |