Page 1 of 4 123 ... LastLast
Results 1 to 10 of 36

Thread: Distribute ranges as per Plan

  1. #1
    Member ayazgreat's Avatar
    Join Date
    Mar 2012
    Posts
    86
    Rep Power
    13

    Distribute ranges as per Plan

    Hi Kris

    I need your help to develop codes which will read ranges (Plan Sheet) data from col a to c and distribute these ranges as per distribution data starting from col e2 (first 3 digits of row first are prefix of range) to onward and copy result to result sheet. I know that somehow it is difficult but you can resolve it.
    I am attaching an example sheet.

    Thanks in advance
    Attached Files Attached Files

  2. #2
    Member Charles's Avatar
    Join Date
    Aug 2012
    Posts
    63
    Rep Power
    13
    Hi,

    In this attachment you will see a button for Test.
    Click it and check Result sheet.
    This code is predicated on the first 3 digit for the start not being with in the entire Start.
    And it will only appear 1 time in the start column.
    This can be modified if necessary.
    Attached Files Attached Files

  3. #3
    Member ayazgreat's Avatar
    Join Date
    Mar 2012
    Posts
    86
    Rep Power
    13
    thanks charles for your reply but after running your code i get totaly wrong result if you see my attched file result sheet then you will get what result should be there.(kindly compare my result sheet data with your result data)

  4. #4
    Member ayazgreat's Avatar
    Join Date
    Mar 2012
    Posts
    86
    Rep Power
    13
    Could anyone please resolve it ?

  5. #5
    Member Charles's Avatar
    Join Date
    Aug 2012
    Posts
    63
    Rep Power
    13
    Hi,

    Too me it appears the code works. In this attachment check the result sheet.
    I have your sample and the code sample.
    If you tested on actual data let me know.
    As mentioned if you are looking at "102" and the start has "102" any place in it the code will send that to the result
    sheet. The code does not look at the first 3 characters of the Start. It can be modified.
    Attached Files Attached Files

  6. #6
    Member ayazgreat's Avatar
    Join Date
    Mar 2012
    Posts
    86
    Rep Power
    13
    In your point of view your code works fine but it does not give correct result, if you copy my result sheet data and paste it in the front of your code result data , then you will be able to find difference, let me explain you through an example if a range start number 100101 to end range number 100105 qty = 5 and i say to distribute this range in three diferent IDs , like ID A , to give 2, ID B to 1, and ID C to 2 then above range is distributed like this

    IDs Start range End range Qty
    a 100101 100102 2
    b 100103 100103 1
    c 100104 100105 2


    It is difficult but can be coded and resolved as given result in my result sheet.

    In your code result is like

    IDs Start range End range Qty
    a 100101 100105 2
    b 100101 100105 1
    c 100101 100105 2
    Last edited by ayazgreat; 12-07-2012 at 01:04 AM.

  7. #7
    Member ayazgreat's Avatar
    Join Date
    Mar 2012
    Posts
    86
    Rep Power
    13
    Could anyone please help ?

  8. #8
    Member ayazgreat's Avatar
    Join Date
    Mar 2012
    Posts
    86
    Rep Power
    13
    Experts, please help to solve this.

  9. #9
    Member Charles's Avatar
    Join Date
    Aug 2012
    Posts
    63
    Rep Power
    13
    HI,

    I'm re-attaching my workbook. If you look at the result sheet you will see we differ.
    I took the time to look this over. The highlighted areas show where there is a problem.
    And as mention my code appears to work.
    The qty comes does not come from the column "Count" it comes from the range for the
    location within your grid.
    If this is wrong please show me the difference.
    If you nor longer wish my help and attempts let me know so that I can spend time
    helping others.
    Attached Files Attached Files

  10. #10
    Member ayazgreat's Avatar
    Join Date
    Mar 2012
    Posts
    86
    Rep Power
    13
    Charles I really appreciate your effort but the problem is still same, I, maybe, can not make you understand what kind of result I want to have if say to you that if a range start from 1027244000 and ends to 10027246187 = Qty 2188 then as per my data range distribution is like that

    my sample
    IDs Start Range End Range Qty
    A 1027244000 1027244443 444
    B 1027244444 1027244943 500
    C 1027244944 1027245043 100
    D 1027245044 1027245287 244
    E 1027245288 1027246087 800
    H 1027246088 1027246187 100


    in your result why next distribution range is not changed

    A 1027244000 1027246187 444
    B 1027244000 1027246187 500
    C 1027244000 1027246187 100
    D 1027244000 1027246187 244
    E 1027244000 1027246187 800
    H 1027244000 1027246187 100

    Why Start Number and End Number is same of ID B,C,D,E,H as same of ID A ? Why isn't range changed after a next ID as per its Plan given Qty, it should be distributed like I am mentioning in my result.

    See my attached workbook how am I distributing the same through a formula only for a single range like I have mentioned above range distribution.
    Attached Files Attached Files

Similar Threads

  1. Replies: 5
    Last Post: 05-28-2013, 03:00 AM
  2. Intersection of Overlapping Ranges:Space Operator
    By Transformer in forum Tips, Tricks & Downloads (No Questions)
    Replies: 0
    Last Post: 05-17-2013, 12:32 AM
  3. List all the name ranges in an excel sheet with scope
    By LalitPandey87 in forum Excel Help
    Replies: 4
    Last Post: 03-28-2012, 07:27 AM
  4. Delete Name Ranges by Scope VBA
    By Admin in forum Excel and VBA Tips and Tricks
    Replies: 0
    Last Post: 12-20-2011, 03:54 AM
  5. List Unique/Common Values From Two Ranges
    By Admin in forum Excel and VBA Tips and Tricks
    Replies: 0
    Last Post: 09-16-2011, 08:34 AM

Posting Permissions

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