+ Reply to Thread
Results 1 to 9 of 9

Generate Random Number for an Unknown Number of Numbers under a Cell Heading in Two Column

  1. #1
    Registered User
    Join Date
    11-07-2013
    Location
    Wixom, MI
    MS-Off Ver
    Excel 2010
    Posts
    4

    Angry Generate Random Number for an Unknown Number of Numbers under a Cell Heading in Two Column

    Ok, so say I have a cell A1 with a heading "ID" with a number 25467 and another heading in C2 named "Number" and the cell under that has the same number 25467.

    The situation? There are an unknown number of rwos under the heading "ID" with 25467 and an unknown number of rows of "Number" with 25467, but the numbers for both headings are the same. Now this is one record of an... Unkown number of records. But all of these are on the same worksheet they just continue on: Example
    A B C
    ID
    25467 Number
    25467 25467
    25467 25467
    25647
    98657 Number
    98657 98657
    98657 98657
    98657 98657


    As you can see the first record has 4 rows under ID and 2 under number and for the second row 4 under ID and 3 under Number.

    How could I create a Macro Button that would change with one click the number for each record in the ID and the Number category "which match" to the same random generated number? which would be unique for each record which isn't seperated by any blank lines in Excel?

  2. #2
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 365
    Posts
    19,952

    Re: Generate Random Number for an Unknown Number of Numbers under a Cell Heading in Two Co

    I am confused by your explanation. Can you upload a sample spreadsheet showing before and after scenarios.

    Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.

    Click on GO ADVANCED and use the paperclip icon to open the upload window.

    View Pic

  3. #3
    Forum Contributor
    Join Date
    03-21-2013
    Location
    Corvallis, OR
    MS-Off Ver
    Excel 2010
    Posts
    174

    Re: Generate Random Number for an Unknown Number of Numbers under a Cell Heading in Two Co

    Do the numbers need to be a consistent number of digits? Like 10000 through 99999, or does it matter/are they different?
    Basically, you need to determine what the minimum number would be and what the maximum number would be, then use the formula
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    . Now if the random generated number has to be unique (not appearing elsewhere in the sheet), this can only be done with vba code, which it seems you are wanting to use anyway. Assuming the button you want to click replaces all existing values in columns A and B that are the same as the selected value with a new random, non-duplicated number between that number range, the code would look like this:
    Please Login or Register  to view this content.

  4. #4
    Registered User
    Join Date
    11-07-2013
    Location
    Wixom, MI
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Generate Random Number for an Unknown Number of Numbers under a Cell Heading in Two Co

    So I modified it a bit. What I need to do now is drop to the next row not by a blank row which is consistent for all records now, but by searching for the next ID in column A that is different than the current record after it has been randomized and changed. I don't really want to look for ID and then offset by 1 but just look for the number. And the ID number for the next record would not have any blank row seperating it from the next record like it has now. ??? I posted a pic and the current code.




    Please Login or Register  to view this content.

    Capture.JPG

  5. #5
    Forum Contributor
    Join Date
    03-21-2013
    Location
    Corvallis, OR
    MS-Off Ver
    Excel 2010
    Posts
    174

    Re: Generate Random Number for an Unknown Number of Numbers under a Cell Heading in Two Co

    To find the next number down in this code, you could use:
    Please Login or Register  to view this content.
    If I helped, please click on Add Reputation.

  6. #6
    Registered User
    Join Date
    11-07-2013
    Location
    Wixom, MI
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Generate Random Number for an Unknown Number of Numbers under a Cell Heading in Two Co

    You notice 7857 under ID in column A:2

    What I mean is if that column A had only one 7857 in it for each record for that column only and then it skipped the blank spaces underneath until it reached the next record below with a different number and then repeated this on down. You can look at the capture.jpg I attached in the previous post. Thanks.

  7. #7
    Forum Contributor
    Join Date
    03-21-2013
    Location
    Corvallis, OR
    MS-Off Ver
    Excel 2010
    Posts
    174

    Re: Generate Random Number for an Unknown Number of Numbers under a Cell Heading in Two Co

    I'm really not grasping what you're trying to accomplish here. Can you explain in any further detail?

  8. #8
    Registered User
    Join Date
    11-07-2013
    Location
    Wixom, MI
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Generate Random Number for an Unknown Number of Numbers under a Cell Heading in Two Co

    Right now the code replaces all the numbers in each record with a random number and scrolls down to the bottom automatically. Each record is separated by a space followed by cell with "ID" written in it. Also Each ID has a number that 7857 in this case that is in each cell in column A all the way down until the last row has no other 7857 in it. This is were the space then seperates the record. Look at the Capture.jpg and you will see what I mean. What I want to do is only have 7857 once in Column A for that record and each preceding record has its own unique number. The catch is that there will be no "ID" written in a cell or blank row between records, it will simply be a new number like 6532 printed once in that cell and the other cells will have 6532 in them like the Capture.jpg shows each record having there own unique numbers. So, how can I generate random numbers for each record of unique numbers where there is no blank row and no "ID" written between cells, just a different set of numbers?

  9. #9
    Forum Contributor
    Join Date
    03-21-2013
    Location
    Corvallis, OR
    MS-Off Ver
    Excel 2010
    Posts
    174

    Re: Generate Random Number for an Unknown Number of Numbers under a Cell Heading in Two Co

    This is still not making any sense... How about you just make a final result capture that shows what you are trying to do so I can just see what it is you want to change. Even better would be to upload a version of your actual workbook that is safe for public viewing.

    In the meantime, are you saying you want each number in each of the lists of IDs that are currently the same to be randomly different from one another except for the first original instance? Also, you keep talking about "no blank rows between records" but your example includes blank rows. Are you trying to say that your actual workbook will not have blank rows, or that you want that to be the end result after the macro runs. Also, in your capture.jpg, there are other columns to the right with duplicated data. Are those supposed to be part of this?

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 5
    Last Post: 06-21-2013, 10:40 AM
  2. [SOLVED] Generate random 6-16-byte random hexadecimal number
    By jsamuelshn in forum Excel General
    Replies: 6
    Last Post: 07-20-2012, 10:38 AM
  3. [SOLVED] generate a random number and use if function to generate new data
    By Dogdoc1142 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 04-25-2006, 10:50 PM
  4. Replies: 6
    Last Post: 08-04-2005, 10:05 PM
  5. Replies: 4
    Last Post: 03-31-2005, 11:06 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1