1. ## 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.

2. Are the points always a multiple of 100?

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

3. 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.

4. provide sample FILE with before/after

5. 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.

6. 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. Nicely done KW!

8. 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.

9. 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```

10. 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
•