Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 142
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 9,101
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 142
Default 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 -


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


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


All times are GMT +1. The time now is 11:19 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"