How to select random Number from a list and populate another sheet

[Lensmeister]

Hi all,

Thought I'd ask if something was possible in a general sense first before getting in to specific details.

I will have a list of numbers in Column A on Sheet 1 along the lines if 1251. 1252, 1555, 4596 (All four digits) one 4 digit number per line. These are fixed numbers and the only ones that can be used.

On Sheet 2 I will have Columns

Column A called Date
Column B called H1
Column C called H2

So now comes the hard question:

What I need to have is something to populate Sheet 2 cell B2 with a number randomly taken from Sheet 1 then repeat for cell C2.
Then repeat this for cell B3, C4, etc down to say line 200.

How on earth do I do this ?

Hope this makes sense.

I have not got a spreadsheet put together yet that I can show.

Thanks in advance,

[BadlySpelledBuoy]

You could apply a random number to each row on Sheet1 using =RAND(), sort the data via that number to randomise them then just take the first half and put it in column B on Sheet2 and the second half and put it on column C on Sheet2.

If you're open to using VBA you can have the whole thing happen in the blink of an eye at the click of a button.

BSB

[Lensmeister]

Thanks.
The 4 digit numbers represent actual cat numbers in a database.
so aren't actually just random numbers there is only one instance of each number.

[BadlySpelledBuoy]

Here's one very straightforward and easy to follow method using VBA.

Please Login or Register  to view this content.

Click the button on Sheet1 in the attached to see it in action.

BSB

[AliGW]

There are instructions at the top of the page explaining how to attach your sample workbook.

A good sample workbook has just 10-20 rows of representative data that has been desensitised. It also has expected results mocked up, relevant cells highlighted and a few explanatory notes.

[BadlySpelledBuoy]

Thanks.
The 4 digit numbers represent actual cat numbers in a database.
so aren't actually just random numbers there is only one instance of each number.

I merely meant adding a random number alongside the numbers you have in order to be able to sort based on those which would randomise the order of the cat numbers.

BSB

[Lensmeister]

Here's one very straightforward and easy to follow method using VBA.

Please Login or Register  to view this content.

Click the button on Sheet1 in the attached to see it in action.

BSB

That is Blooming AMAZING ... thanks Exactly how I'd imagined the results to look like.
I'll read through the VBA Reputations added to say thanks

[Lensmeister]

There are instructions at the top of the page explaining how to attach your sample workbook.

A good sample workbook has just 10-20 rows of representative data that has been desensitised. It also has expected results mocked up, relevant cells highlighted and a few explanatory notes.

Thanks
Normally I would have had a sample set of data and done that buy as I mentioned I hadn't got a spreadsheet together as yet as I want to know if it could be done.

BSB has hit the nail on the head perfectly.

[BadlySpelledBuoy]

Glad I could help.

I can be improved upon with things like putting the data back in the original order, if necessary, or made dynamic to cater for varying list lengths etc. But should be a step in the right direction for ya

BSB

[Lensmeister]

It is.

I will get a full list of the Cat Numbers ot of the DB we have then able to paste them in to Sheet 1 Column A

Those are the numbers that'll need to the randomising in to Sheet 2.

[BadlySpelledBuoy]

Depending on the database software you use you could have Excel reach in and get the numbers for you. But that means the code gets a little more indepth and technical.

Good luck with it. Give me a shout if you need it amending in any way.

BSB