Jump to content

Welcome to Geeks to Go - Register now for FREE

Geeks To Go is a helpful hub, where thousands of volunteer geeks quickly serve friendly answers and support. Check out the forums and get free advice from the experts. Register now to gain access to all of our features, it's FREE and only takes one minute. Once registered and logged in, you will be able to create topics, post replies to existing threads, give reputation to your fellow members, get your own private messenger, post status updates, manage your profile and so much more.

Create Account How it Works
Photo

Help with Excel


  • Please log in to reply

#1
diggeryo

diggeryo

    Member

  • Member
  • PipPipPip
  • 343 posts
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.
  • 0

Advertisements


#2
diggeryo

diggeryo

    Member

  • Topic Starter
  • Member
  • PipPipPip
  • 343 posts
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.
  • 0

#3
diggeryo

diggeryo

    Member

  • Topic Starter
  • Member
  • PipPipPip
  • 343 posts
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.
  • 0






Similar Topics

0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users

As Featured On:

Microsoft Yahoo BBC MSN PC Magazine Washington Post HP