Macro to copy random cell values and assign it different gro

December 4, 2013 at 23:22:47
Specs: Windows 7
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.

See More: Macro to copy random cell values and assign it different gro

Report •

#1
December 6, 2013 at 13:10:59
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.


Report •

#2
December 6, 2013 at 22:54:26
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 at

http://jmp.sh/b/WIywltalGE7bZ4sUv2c8


hope you can help me again.

message edited by sunny2u86


Report •

#3
December 7, 2013 at 17:05:09
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.


Report •

Related Solutions

#4
December 7, 2013 at 18:52:09
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..


Report •

#5
December 7, 2013 at 19:02:44
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.


Report •

#6
December 8, 2013 at 23:41:10
please help me with your last suggestion..

Report •

#7
December 9, 2013 at 08:05:06
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 pretty random.

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.


Report •

#8
December 12, 2013 at 05:26:21
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. .

Report •

Ask Question