How to pick 50 numbers randomly

[chavanalini]

Hi,

In sheet1 columnA I have some data from that i want to copy randomly 50 rows to sheet2. Please help me is there any formula to do this


Thank You

[zbor]

Try this in Sheet2 (From 2nd row!)

Please Login or Register  to view this content.

[Richard Buttrey]

It would be easier if we could see your data, and know whether it has to remain in place or not.

However one way. Add a helper column next to your data and populate it with =RAND()

Then sort all your data using the Rand column as the key then just copy the to 50 rows and paste to sheet2.

You could put all that together in a macro if you're repeating it many times.

[chavanalini]

Thanks for your immediate response,

I will explain clearly

In my attached file in column A i have some numbers from A2 to A452 from this sheet i want to copy 50 numbers (randomly) to sheet2, but this not one time exercise i have to do this for more than 100 excel files, each excel file is having different number of rows minimum number of rows is 50 maximum is 1000. means one excel file is having only 150 rows, another excel file is having 600 rows like that each excel file is having different number of rows. but from each file i want 50 random numbers.

I think i explained clearly

Thank you

[Richard Buttrey]

Hi,

Have you tried either of the answers you've been given?

Zbor's answer should work with any data.

[chavanalini]

I tried Zbor's but in all rows it is showing '0' only not getting any value

Thank u

[satabp]

Hi Nalini,

I hope you are trying to put the formula in Column A of Sheet2... if you do like that you will get circular error and returns ZERO.

Hence please try to put the formula in Column B with the following formula from Mr. Zbor..

=INDEX(Sheet1!A:A,RANDBETWEEN(1,COUNTA(Sheet1!A:A)))

But, this will gives you duplicate numbers. I suggest to have a macro for this as suggested by Mr. Richard.

I do same kind of activity for random quality sampling..

Please let me know, if you need help in having the macro created for you problem...

Thanks,
BP

[chavanalini]

Thank you so much it is working, but I dont want the out put as numbers, means in my excel file i have given some 'IDs' in ColumnA from that I want to take 50 IDs randomly and put it in sheet2, the number of rows in each file is not same (minimum 50 and maximum 1000)

Please help me is there any formula to pick only 50 samples from the given set

Thank you

[chavanalini]

Thank you so much it is working, but I dont want the out put as numbers, means in my excel file i have given some 'IDs' in ColumnA from that I want to take 50 IDs randomly and put it in sheet2, like this I have 100 files the number of rows in each file is not same (minimum 50 and maximum 1000)

Please help me is there any formula to pick only 50 samples from the given set

Thank you

[chavanalini]

Hi,

I need the selected rows to be displayed in sheet2. The idea being that I have Employees data in sheet1 with hundreds of IDs and I only want a random selection of 50 IDs to appear in sheet2 in excel

Please help me is there any formula to do this excercise

Thank you

[adyteo]

Column A: Employee ID
Column B: =RANDBETWEEN(1,COUNTA(A:A)-1)
Column C: =IF(RANK.AVG(B2,B:B)<51,A2,"")

this will show you, in column C, the Top 50.

[chavanalini]

Hi,

This is working but out put is not coming what I am expecting.

If this is not possible please tell me how to pick first 50 rows from sheet1 and put it in sheet2

Thanks you

[Richard Buttrey]

Is post #3 not an option if you're having difficulty with the other suggestions?

[adyteo]

create a Module and paste the following code. then you would need to run the Macro Top50Numbers (Alt+F8) then select the Top50Numbers and click Run.
Do not forget to save the file as XLSM (Macro Enabled WorkSheet)

Please Login or Register  to view this content.

[chavanalini]

Hi,

Please help me how to pick 50 values randomly from sheet1 column A and put it in sheet2 Column A

Please help me is there any formula to do this

Thank you

[Richard Buttrey]

See Attached.

I still can't help thinking that a simple macro that first sorts the data plus a RAND column on the Rand value and then copies the first 50 records to sheet 2 is simpler