+ Reply to Thread
Results 1 to 13 of 13

Populate numbers to specific range

  1. #1
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,882

    Populate numbers to specific range

    Hello everyone
    I have numeric values in column A (A1:A5) and in column B I put the number of desired occurence for each number ..
    In cell E1 I put the number of the desired rows and in cell F1 I put the number of the desired columns

    The desired results would start in cell D10 ...

    I need to randomize numbers based on number of occurences in the desired range("D10:G14") ...
    Hope it is clear
    Attached Files Attached Files
    < ----- Please click the little star * next to add reputation if my post helps you
    Visit Forum : From Here

  2. #2
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Populate numbers to specific range

    Row\Col
    A
    B
    C
    D
    E
    F
    1
    Numbers
    Reps
    Helper
    2
    5
    1
    0
    C2: =SUM(C1, B1)
    3
    3
    6
    1
    4
    4
    3
    7
    5
    2
    2
    10
    6
    1
    4
    12
    7
    3
    4
    16
    8
    9
    10
    1
    2
    1
    5
    A10:D14: {=INDEX($A$2:$A$7, MATCH(randlong() - 1, $C$2:$C$7))}
    11
    3
    3
    1
    3
    12
    2
    3
    4
    3
    13
    3
    3
    4
    3
    14
    1
    3
    4
    3


    Please Login or Register  to view this content.
    Entia non sunt multiplicanda sine necessitate

  3. #3
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,882

    Re: Populate numbers to specific range

    Thank you very much Mr. shg for this fascinating solution. It is perfect.
    Just a little question : Can I depend on the the cells E1 and F1 to determine the number of rows and number of columns desired ..
    And if for example the no of rows = 7 and the no of columns =4 so the no of cells will be = 28 ..As for results would be just 20 in the sample attached ..
    So in this case to distribute numbers and the rest of cells ( 28- 20 = 8) which will be eight cells to be empty..
    Hope it is clear
    Thanks a lot for your great help

  4. #4
    Forum Guru karedog's Avatar
    Join Date
    10-03-2014
    Location
    Indonesia
    MS-Off Ver
    2003
    Posts
    2,971

    Re: Populate numbers to specific range

    Maybe :

    Please Login or Register  to view this content.

  5. #5
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,882

    Re: Populate numbers to specific range

    Mr. Karedog
    You are perfect ... this is very very awesome solution
    Thank you very much for this great and wonderful gift

  6. #6
    Forum Guru karedog's Avatar
    Join Date
    10-03-2014
    Location
    Indonesia
    MS-Off Ver
    2003
    Posts
    2,971

    Re: Populate numbers to specific range

    You are welcome, glad to help.


    Regards

  7. #7
    Forum Expert sourabhg98's Avatar
    Join Date
    10-22-2014
    Location
    New Delhi, India
    MS-Off Ver
    Excel 2007, 2013
    Posts
    1,899

    Re: Populate numbers to specific range

    I also tried making a macro-

    Please Login or Register  to view this content.
    For this macro you need to have a helper sheet named "Random".

    I know this is not a very good macro but this is what I could make with my limited VBA knowledge.

    Check attached.
    Attached Files Attached Files
    Happy to Help

    How to upload excel workbooks at this forum - http://www.excelforum.com/the-water-...his-forum.html

    "I don't get things easily, so please be precise and elaborate"

    If someone's post has helped you, thank by clicking on "Add Reputation" below the post.
    If your query is resolved please mark the thread as "Solved" from the "Thread Tools" above.

    Sourabh

  8. #8
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,882

    Re: Populate numbers to specific range

    Thank you very much Mr. Sourabh for this great solution. It is wonderful and very helpful too
    Best Regards

  9. #9
    Forum Expert sourabhg98's Avatar
    Join Date
    10-22-2014
    Location
    New Delhi, India
    MS-Off Ver
    Excel 2007, 2013
    Posts
    1,899

    Re: Populate numbers to specific range

    Glad to know that helped!
    Thank You for the feedback and reputation.

  10. #10
    Forum Expert sourabhg98's Avatar
    Join Date
    10-22-2014
    Location
    New Delhi, India
    MS-Off Ver
    Excel 2007, 2013
    Posts
    1,899

    Re: Populate numbers to specific range

    I just learned how to use the arrays in VBA and made my first code using arrays-
    This one works without helper sheet.
    I know you have enough solutions, I just wanted to share-
    Please Login or Register  to view this content.
    Attached Files Attached Files

  11. #11
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,882

    Re: Populate numbers to specific range

    Thanks a lot for plenty solution but it doesn't work ..it gives errors

  12. #12
    Forum Expert sourabhg98's Avatar
    Join Date
    10-22-2014
    Location
    New Delhi, India
    MS-Off Ver
    Excel 2007, 2013
    Posts
    1,899

    Re: Populate numbers to specific range

    Error Rectified-
    Please Login or Register  to view this content.
    Attached Files Attached Files

  13. #13
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,882

    Re: Populate numbers to specific range

    Thanks a lot for this great solution. Now it works like charm
    Best regards

+ 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. [SOLVED] Populate data according to specific numbers
    By YasserKhalil in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 10-27-2014, 08:17 PM
  2. Populate a specific range based on calendar date
    By gottnoskill in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-28-2014, 03:19 PM
  3. [SOLVED] How to populate a range with sequential numbers?
    By GIS2013 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 09-04-2013, 03:34 AM
  4. Replies: 3
    Last Post: 03-05-2012, 12:11 AM
  5. Replies: 3
    Last Post: 03-18-2011, 12:37 AM
  6. Replies: 15
    Last Post: 10-11-2009, 11:46 AM

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