hi

i have a spread sheet with several rows(1000).

i need to select every cell in a range(A1 to A 1000) and assign these values to 5 different groups randomly without any duplicate values.

Here's one possible method.... First, I'm going to assume that you have your values in Sheet3!A1:A1000. That just happens to be how I set my workbook up, the code can easily be changed to use a different sheet.

First we need to create a list of numbers from 1 to 1000 in random order.

I did this as follows:

1 - Fill Sheet1!A1:A1000 with 1-1000.

2 - Fill B1:B1000 with =RAND().

3 - Sort Columns A&B by Column B to randomize your 1-1000 numbers.

4 - Delete Column B.Next, the titles for my 5 groups were placed in E1:I1 of the same sheet as the randomized list of 1-1000.

I then ran the following code, which will use the numbers in Column A of Sheet1 as Row numbers to pull values from Sheet3 into the table in E:J, in groups of 200. Since the Rows number are non-repeating and in random order, your values from Sheet3 will be grouped "randomly" into the 5 groups.

Sub RndValues() 'Initialize variable for Start Row and Destination Row stRw = 1 dstRw = 1 'Loop through 5 Groups (Columns E:I) For col = 5 To 9 'Loop through Column A in Groups of 200 For srcRw = stRw To stRw + 199 'Increment Destination Row dstRw = dstRw + 1 'Place value from Sheet 3 Column A in Column E:I based 'on Row Number from Sheet1 Column A Cells(dstRw, col) = Sheets(3).Range("A" & Cells(srcRw, 1)) Next 'Reset Destination Row dstRw = 1 'Set Start Row for next group of 200 stRw = stRw + 200 Next End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Hi, i really glad you helped me but when i ran this code it is showing error

for line "Cells(dstRw, col) = Sheets(3).Range("A" & Cells(srcRw, 1))"error message:"Application or object define error"

for more fair idea i'm attaching a sample sheet.

in sheet1 we have ticket no in A1:A to any range,

then title in B1:B and then finally 5 groups(5 sheets-group1, group2 and so on..)

please fine the file athttp://jmp.sh/b/WIywltalGE7bZ4sUv2c8

hope you can help me again.

message edited by sunny2u86

It looks to me like your spreadsheet is sorted in alphabetical order by title. The ticket numbers look to be completely random. Can't you sort by the ticket number and then just move sections to each group? Wouldn't that result in enough randomness?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

That can be helpful as well..

All the thing that I want to do is with the ticket number.if randomised can be perform on it then it is what I required. .

I think that can be possible..Thank you once again..

I'm not sure what you mean by your last post. Will my last suggestion work for you or do you still need help with this?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

please help me with your last suggestion..

My last suggestion was this: When I looked at your spreadsheet I noticed 2 things:

1 - The Titles in Column B are listed in alphabetical order.

2 - The Ticket Numbers in Column A appear to be a list of numbers with no discernable pattern. In other words, they already seem prettyrandom.If you sorted your sheet by Column A (the Ticket Numbers) the Titles would get all "mixed up" since the Ticket Numbers - which already appear random - would be placed in numberical order.

Now, if you have 2000 rows and 5 groups, you could just copy blocks of 400 rows to each "Group sheet".

If this won't give you the results you are looking for, please explain why so that I'll have a beter idea of what you are looking for.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Hi again..

Yoi are right that title are arranged in alphabetical order but that just by chance..

My requirment is only with the tickets. And yes you are correct again then are completely random. .

All I want is that any ticket could be assigne to any of the five group randomly with no duplication. .

And their may be chance when the xount of ticket no may be increased...

I hope I could clear some of your doubt. .

Ask Your Question

Weekly Poll

Would you ride in a self-driving car from Tesla?

Discuss in The Lounge

Poll History