Forum Discussion
falguthmy
Apr 26, 2020Copper Contributor
Formula to select random cell value from multiple columns without duplicates
So I have 7 columns (groups) of data. I'd like to randomly select a cell value from any of the groups several times (say, 5 times) without duplicates. The results should be categorised into their res...
Riny_van_Eekelen
Apr 27, 2020Platinum Contributor
falguthmy Not sure what your intentions are with the columns rilled with =RAND(), but I believe the attached workbook (made on a Mac) contains what you asked for, provided that your Excel supports dynamic array functions.
I slightly changed the layout of your worksheet and introduced a hidden "helper section" below row 19. Here, a list of random numbers is calculated from 1 to 49 (being the number of items in the 7 groups). Then the first 5 are selected and the the corresponding value from the groups above is picked-up and categorised in the correct group.