|
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 |
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 |
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 - |
| All times are GMT +1. The time now is 08:38 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com