Formula to select random cell value from multiple columns without duplicates

%3CLINGO-SUB%20id%3D%22lingo-sub-1339340%22%20slang%3D%22en-US%22%3EFormula%20to%20select%20random%20cell%20value%20from%20multiple%20columns%20without%20duplicates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1339340%22%20slang%3D%22en-US%22%3E%3CP%3ESo%20I%20have%207%20columns%20(groups)%20of%20data.%20I'd%20like%20to%20randomly%20select%20a%20cell%20value%20from%20any%20of%20the%20groups%20several%20times%20(say%2C%205%20times)%20without%20duplicates.%20The%20results%20should%20be%20categorised%20into%20their%20respective%20groups%20i.e.%20if%20cell%20B8%20was%20the%20random%20selection%20and%20is%20from%20group%201%2C%20the%20result%20should%20be%20displayed%20in%20group%201%20in%20another%20table%20(see%20sample%20file).%20What%20would%20the%20formula%20look%20like%3F%20Thanks%20for%20your%20help!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1339340%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20on%20Mac%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1339639%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20to%20select%20random%20cell%20value%20from%20multiple%20columns%20without%20duplicates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1339639%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F637688%22%20target%3D%22_blank%22%3E%40falguthmy%3C%2FA%3E%26nbsp%3BNot%20sure%20what%20your%20intentions%20are%20with%20the%20columns%20rilled%20with%20%3DRAND()%2C%20but%20I%20believe%20the%20attached%20workbook%20(made%20on%20a%20Mac)%20contains%20what%20you%20asked%20for%2C%20provided%20that%20your%20Excel%20supports%20dynamic%20array%20functions.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20slightly%20changed%20the%20layout%20of%20your%20worksheet%20and%20introduced%20a%20hidden%20%22helper%20section%22%20below%20row%2019.%20Here%2C%20a%20list%20of%20random%20numbers%20is%20calculated%20from%201%20to%2049%20(being%20the%20number%20of%20items%20in%20the%207%20groups).%20Then%20the%20first%205%20are%20selected%20and%20the%20the%20corresponding%20value%20from%20the%20groups%20above%20is%20picked-up%20and%20categorised%20in%20the%20correct%20group.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Visitor

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 respective groups i.e. if cell B8 was the random selection and is from group 1, the result should be displayed in group 1 in another table (see sample file). What would the formula look like? Thanks for your help!

1 Reply
Highlighted

@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.