Remember Me?

#1
February 14th 08, 11:19 AM posted to microsoft.public.excel.misc
 external usenet poster First recorded activity by ExcelBanter: Mar 2007 Posts: 142
Formula for list summary

I have a list below with min & max values. I want a summary in such a
way that the formula calculates the first number in range & last
number and continue for any skiped series.

List

12220
12221
12222
12223
12224
12225
12226
12227
133356
133357
133359
133360
133361
133362
133363
133364
________________________

Result:
Start End Quantity
12220 12227 8
133356 133357 2
133359 133364 6

#2
February 14th 08, 12:49 PM posted to microsoft.public.excel.misc
 external usenet poster First recorded activity by ExcelBanter: Jul 2006 Posts: 9,101
Formula for list summary

Sub test()

Range("D1") = "Start"
Range("E1") = "End"
Range("F1") = "Count"
ResultRow = 2
StartRow = 1
RowCount = StartRow
StartNumber = Range("A" & RowCount)
Do While Range("A" & RowCount) < ""
CountNumber = Range("A" & RowCount)
If (CountNumber + 1) < Range("A" & (RowCount + 1)) Then
Range("D" & ResultRow) = StartNumber
Range("E" & ResultRow) = CountNumber
Range("F" & ResultRow) = CountNumber - StartNumber + 1
ResultRow = ResultRow + 1
StartNumber = Range("A" & (RowCount + 1))
End If
RowCount = RowCount + 1
Loop

End Sub
"Sinner" wrote:

I have a list below with min & max values. I want a summary in such a
way that the formula calculates the first number in range & last
number and continue for any skiped series.

List

12220
12221
12222
12223
12224
12225
12226
12227
133356
133357
133359
133360
133361
133362
133363
133364
________________________

Result:
Start End Quantity
12220 12227 8
133356 133357 2
133359 133364 6

#3
February 14th 08, 01:22 PM posted to microsoft.public.excel.misc
 external usenet poster First recorded activity by ExcelBanter: Mar 2007 Posts: 142
Formula for list summary

Thanks Joel.
As always you have been great help : )

Regards,

Sinner

On Feb 14, 4:49*pm, Joel wrote:
Sub test()

Range("D1") = "Start"
Range("E1") = "End"
Range("F1") = "Count"
ResultRow = 2
StartRow = 1
RowCount = StartRow
StartNumber = Range("A" & RowCount)
Do While Range("A" & RowCount) < ""
* *CountNumber = Range("A" & RowCount)
* *If (CountNumber + 1) < Range("A" & (RowCount + 1)) Then
* * * Range("D" & ResultRow) = StartNumber
* * * Range("E" & ResultRow) = CountNumber
* * * Range("F" & ResultRow) = CountNumber - StartNumber + 1
* * * ResultRow = ResultRow + 1
* * * StartNumber = Range("A" & (RowCount + 1))
* *End If
* *RowCount = RowCount + 1
Loop

End Sub

"Sinner" wrote:
I have a list below with min & max values. I want a summary in such a
way that the formula calculates the first number in range & last
number and continue for any skiped series.

List

12220
12221
12222
12223
12224
12225
12226
12227
133356
133357
133359
133360
133361
133362
133363
133364
________________________

Result:
Start * End * * Quantity
12220 * 12227 * 8
133356 *133357 *2
133359 *133364 *6- Hide quoted text -

- Show quoted text -

 Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On

 Similar Threads Thread Thread Starter Forum Replies Last Post UKMAN Excel Worksheet Functions 2 September 26th 07 10:56 AM [email protected] Excel Worksheet Functions 0 September 7th 06 12:36 AM [email protected] Excel Discussion (Misc queries) 0 September 6th 06 11:58 PM Stephen - Dallas Excel Discussion (Misc queries) 2 May 3rd 06 02:51 PM Tray Excel Discussion (Misc queries) 2 December 17th 04 03:47 AM

All times are GMT +1. The time now is 06:29 AM.