Results 1 to 10 of 10
Thread: Divide into Group

20141028, 16:34 #1
 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 DCol E Col F
No.IdStartingPointsPlanEvent
1.12341500Indoor12102014
[If Col D more than 100 then divide it into equals of 100]
1.12341100Indoor 12102014
2.1234101200Indoor12102014
3.1234201300Indoor12102014
4.1234301400Indoor12102014
5.1234401500Indoor12102014
Any Helps.Last edited by carmine; 20141028 at 16:38.

20141028, 20:46 #2
 Join Date
 Jan 2001
 Location
 La Jolla, CA
 Posts
 1,495
 Thanks
 33
 Thanked 63 Times in 59 Posts
Are the points always a multiple of 100?
Where are the new splitouts to be? In new sheets? Group by Group?

20141029, 03:48 #3
 Join Date
 Nov 2013
 Posts
 40
 Thanks
 3
 Thanked 0 Times in 0 Posts
Hello,
Are the points always a multiple of 100?
Where are the new splitouts to be? In new sheets? Group by Group?
Thanks in advanceLast edited by carmine; 20141029 at 03:51.

20141029, 08:07 #4
 Join Date
 Apr 2014
 Location
 Austin, TX
 Posts
 252
 Thanks
 1
 Thanked 36 Times in 34 Posts
provide sample FILE with before/after

20141029, 11:47 #5
 Join Date
 Jan 2001
 Location
 La Jolla, CA
 Posts
 1,495
 Thanks
 33
 Thanked 63 Times in 59 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.Last edited by kweaver; 20141029 at 16:18.

20141031, 17:02 #6
 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.

20141031, 17:30 #7
 Join Date
 Aug 2010
 Location
 Pa, USA
 Posts
 2,733
 Thanks
 126
 Thanked 686 Times in 623 Posts
Nicely done KW!

20141031, 17:42 #8
 Join Date
 Jan 2001
 Location
 La Jolla, CA
 Posts
 1,495
 Thanks
 33
 Thanked 63 Times in 59 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.Last edited by kweaver; 20141031 at 17:48.

20141031, 19:54 #9
 Join Date
 Aug 2010
 Location
 Pa, USA
 Posts
 2,733
 Thanks
 126
 Thanked 686 Times in 623 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
Last edited by Maudibe; 20141031 at 23:33.

20141101, 03:08 #10
 Join Date
 Nov 2013
 Posts
 40
 Thanks
 3
 Thanked 0 Times in 0 Posts
Hi,
AWESOME kweaver & Maud.
Thanks