Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Creating summary from a list | Excel Worksheet Functions | |||
Creating a Detailed List from a Summary List | Excel Worksheet Functions | |||
Creating A Breakout List from a Summary List | Excel Discussion (Misc queries) | |||
Pulling a Summary List from a Larger List | Excel Discussion (Misc queries) | |||
Summary List?? | Excel Discussion (Misc queries) |