Formula for list summary

S

Sinner

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
 
J

Joel

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
 
Top