Results 1 to 10 of 10
  1. #1
    Lounger
    Join Date
    Nov 2013
    Posts
    40
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Divide into Group

    Hello,

    I have points in Column D wherein if the value is greater than 100 then divide it into group.

    Col A---------- Col B---------- Col C---------- Col D----------Col E---------- Col F
    No.--------------Id------------Starting--------Points---------Plan------------Event
    1.--------------1234------------1--------------500----------Indoor--------12-10-2014

    [If Col D more than 100 then divide it into equals of 100]

    1.--------------1234-------------1-------------100----------Indoor------- 12-10-2014
    2.--------------1234------------101------------200----------Indoor-------12-10-2014
    3.--------------1234------------201------------300----------Indoor-------12-10-2014
    4.--------------1234------------301------------400----------Indoor-------12-10-2014
    5.--------------1234------------401------------500----------Indoor-------12-10-2014

    Any Helps.
    Last edited by carmine; 2014-10-28 at 16:38.

  2. #2
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    Are the points always a multiple of 100?

    Where are the new split-outs to be? In new sheets? Group by Group?

  3. #3
    Lounger
    Join Date
    Nov 2013
    Posts
    40
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Hello,

    Are the points always a multiple of 100?
    No, if its greater than 100 than split it into 100 each. if Col D points are 150 then split 100 & second one 50.

    Where are the new split-outs to be? In new sheets? Group by Group?
    In the same sheet in descending row order.

    Thanks in advance
    Last edited by carmine; 2014-10-29 at 03:51.

  4. #4
    3 Star Lounger Supershoe's Avatar
    Join Date
    Apr 2014
    Location
    Austin, TX
    Posts
    252
    Thanks
    1
    Thanked 36 Times in 34 Posts
    provide sample FILE with before/after

  5. #5
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    And, does the start number always start with 1? Suppose it's 10 and the other number is 400 ?

    From the starting point, the first ending point is 99+starting point.
    Then, the next starting point would be 1+the previous ending point.
    And, so on...

    Is this what you want?

    See the attached for a guess at what you're trying to do.
    Attached Files Attached Files
    Last edited by kweaver; 2014-10-29 at 16:18.

  6. #6
    Lounger
    Join Date
    Nov 2013
    Posts
    40
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Thanks kweaver.

    Exactly thats what i was looking for.

    One thing if the number is increased in thousand than it does not give the output.
    Like starting 17500 Points 2000 How to do that.
    Can we convert this into macro.
    Thanks.

  7. #7
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,634
    Thanks
    115
    Thanked 649 Times in 592 Posts
    Nicely done KW!

  8. #8
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    There's no macro...just formulas. Go to the last bordered row (I think it was 11) and fill the cells from H to M down as you need them. Per the attached.

    Just attached another.
    Attached Files Attached Files
    Last edited by kweaver; 2014-10-31 at 17:48.

  9. #9
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,634
    Thanks
    115
    Thanked 649 Times in 592 Posts
    Carmine,

    If needed, consider the following code. It will allow you to input an additional parameter of the increment between Points (cell G2) then calculate each row in the grid up to the remainder without exceeding Points. Place in a standard module.

    HTH,
    Maud

    DivideIntoGroups1.png

    Code:
    Dim Starting As Long, Counter As Integer
    
    
    Public Sub GroupNums()
    '----------------------------------
    'DECLARE AND SET VARIABLES
    Dim Points As Long, EndPoint As Long
    Dim Increment As Integer, Remainder As Integer
    Increment = [g2]
    Starting = [c2]
    Points = [d2]
    Remainder = Points Mod Increment
    Cycles = Round(Points / Increment, 0)
    EndPoint = [c2] + [g2]
    '----------------------------------
    'INSERT ROWS USING UP POINTS IN INCREMENTS
    For Counter = 2 To Cycles + 1
        SplitNums EndPoint
        Starting = EndPoint
        EndPoint = EndPoint + Increment
    Next Counter
    '----------------------------------
    'LAST ROW WITH REMAINDER
    If Remainder > 0 Then SplitNums Starting + Remainder
    End Sub
    
    
    Public Sub SplitNums(EndPt As Long)
    '----------------------------------
    'FILL ROWS
        Range("H" & Counter) = [a2]
        Range("I" & Counter) = [b2]
        Range("J" & Counter) = Starting
        Range("K" & Counter) = EndPt
        Range("L" & Counter) = [e2]
        Range("M" & Counter) = [f2]
    End Sub
    Attached Files Attached Files
    Last edited by Maudibe; 2014-10-31 at 23:33.

  10. #10
    Lounger
    Join Date
    Nov 2013
    Posts
    40
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Hi,

    AWESOME kweaver & Maud.

    Thanks

Posting Permissions

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