Pick random 4 cells

[stribor40]

I am trying to pick 4 cells from the row of cells. For example if I have column A1 (1000 rows) how would I randomly pick 4 of these cells.

Thanks

[Glenn Kennedy]

It's not entirely clear what you want... but try this aray formula in C1

="A"&LARGE(ROW($1:$1000)*NOT(COUNTIF($c$1:c1, ROW($1:$1000))), RANDBETWEEN(1,1001-ROW(A1)))

drag down for four rows.

Array Formulae are a little different from ordinary formulae in that they MUST be confirmed in the FIRST CELL ONLY by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. After that, the array can be dragged down as normal, to cover the desired range.

You will know the array is active when you see curly brackets { } - or "curly braces" for those of you in the USA, or "flower brackets" for those of you in India - appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly brackets yourself - it won't work...

[stribor40]

I have column of id's and I am trying to randomly pick 4 of these id's to win the prize

[Glenn Kennedy]

=INDEX($A$1:$A$1000,LARGE(ROW($1:$1000)*NOT(COUNTIF($C$1:C1, ROW($1:$1000))), RANDBETWEEN(1,1001-ROW(A1))))

Bear in mind that the selected values will change when anything else changes on the sheet. If you want to keep th numbers, copy/paste values. Or... you will require VBA

[stribor40]

ok Thank you

[Glenn Kennedy]

Alternatively:

Please Login or Register  to view this content.

How to install your new code
Copy the Excel VBA code
Select the workbook in which you want to store the Excel VBA code
Press Alt+F11 to open the Visual Basic Editor
Choose Insert > Module
Edit > Paste the macro into the module that appeared
Close the VBEditor
Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)
[B][I]


Select 4 cells Paste this into the formula bar and commit with CTRl-Shift-Enter. this is non-volatile. and will not recalculate. Enable macros on opening

[stribor40]

How would I do this formula if i have only 350 rows?

Never mind this question. Sorry )

[Glenn Kennedy]

I assume that you are now OK with the answer. If so, You're welcome.

---

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.