[diggeryo]

I have Excel 2007 with SP1 and all the latest updates.

In my Excel file, I have 16,000 rows. In column AH, I have a number from 1 through 400 so that there are 40 of each number in column AH in the entire spreadsheet.

I have to split the sheet into multiple sheets to send to four different people. Each person should get 4,000 rows, which will contain 100 of the numbers in column AH. I cannot split the numbers in column AH among different people. So, for example, each of the 40 rows that have AH=100 must go to the same person.

I could simply send AH=1-100 to one person, AH=101-200 to the second person, etc. However, it is important that the AH numbers each person gets are NOT sequential.

I was thinking I could create a new column with another random number from 1 through 400 and split it according to that. But is there a way to make sure that each row that has the same AH number gets the same random number?

Any ideas would be greatly appreicated...

mike.

[Advertisements]

To answer my own question, in the first cell of the new column, I would put:

=RANDBETWEEN(1,400)

Then in every remaining cell, I would put:

=IF(AH2=AH1,AP1,RANDBETWEEN(1,400))

However, this does what I want in that it makes sure any row with the same number in AH has the same random number. However, I do need one other thing. Is there any way to make sure that when a random number is chosen, it doesn't already appear in the list?

thanks,
mike.

[diggeryo]

To answer my own question, in the first cell of the new column, I would put:

=RANDBETWEEN(1,400)

Then in every remaining cell, I would put:

=IF(AH2=AH1,AP1,RANDBETWEEN(1,400))

However, this does what I want in that it makes sure any row with the same number in AH has the same random number. However, I do need one other thing. Is there any way to make sure that when a random number is chosen, it doesn't already appear in the list?

thanks,
mike.

[diggeryo]

Again, answering my own question... well, not really answering the question, but I found another way to do what I am trying to do...

*Sort Excel file by the column with the 1 through 400 number in it (so it is 1,2,3,4, etc.)
*go to ( http://www.pangloss....del/rnumber.cgi ) and create random numbers from 1 to 401 (use newline delimeter)
*highlight all the numbers and copy them, then paste them into the next blank column in the Excel file
*for remaining blank cells in new column, put this formula there: =IF(AH3=AH2,AQ2) , where AH is the column with the 1 through 400 number and AQ is the new column
*sort by AH column
*right-click and copy the new column, then Paste Special/Values the new column

I now have random numbers in that column where all of the same AH numbers have the same random number.