Range synopsis

[pradyuthal]

1 1 8
2 10
3 20 29
4 44 45
5
6
7
8
10
20
21
22
23
24
25
26
27
28
29
44
45

I enter data in column a and like to have the synopsis as shown in column b & c.

How do I perform the task as i have huge such data to prepare a report in this way.
Please help.

Thanks a lot.

 

[Norie]

Can you explain the synopsis?

How do you arrive at the values in column B & C?

 

[mikerickson]

What result would you want from

1
2
3
6
5
7
8
9
10
11
12

 

[pradyuthal]

I enter 1 to 8 cells a1 through a8 but it should show as 1 in cell b1 and 8 in cell c1

 

[pradyuthal]

No i have no number serially. there are several left out numbers also. such I have numbers in column a 1 to 8 , 9 is missing , again 10 then continues from 20 to 29 and so on. But the it should be displayed as 1 to 8 , 10 to 0 , 20 to 29 using two columns B & C.

 

[Norie]

So you just want to find all the sets of consecutive numbers in the numbers you enter?

Or are you actually trying to identify gaps in the data?

 

[Rick Rothstein]

Does this do what you want?

Sub CreateSynopsis()
  Dim X As Long, LastRowA As Long, LastRowB As Long, StartNumber As Long
  Const DataStartRow As Long = 1
  LastRowA = Cells(Rows.Count, "A").End(xlUp).Row
  StartNumber = Cells(DataStartRow, "A").Value
  For X = DataStartRow + 1 To LastRowA + 1
    If Cells(X, "A").Value - Cells(X - 1, "A").Value <> 1 Then
      LastRowB = Cells(Rows.Count, "B").End(xlUp).Row
      If DataStartRow = 1 And LastRowB = 1 Then
        If Cells(1, "B").Value <> "" Then LastRowB = LastRowB + 1
      Else
        LastRowB = LastRowB + 1
      End If
      Cells(LastRowB, "B").Value = StartNumber
      If StartNumber <> Cells(X - 1, "A").Value Then Cells(LastRowB, "C").Value = Cells(X - 1, "A").Value
      StartNumber = Cells(X, "A").Value
    End If
  Next
End Sub

Change the DataStartRow constant (the Const statement) to match your actual setup.

 

[mikerickson]

This can be done with the CSE formulas
=MIN(IF(((ROW(1:1)-1)*10<=$A$1:$A$100)*($A$1:$A$100 < ROW(1:1)*10)*(ISNUMBER($A$1:$A$100)), $A$1:$A$100))
and
=MAX(IF(((ROW(1:1)-1)*10<=$A$1:$A$100)*($A$1:$A$100 < ROW(1:1)*10)*(ISNUMBER($A$1:$A$100)), $A$1:$A$100))

both of which need to be entered with Ctrl-Shift-Enter (Cmd+Return for Mac)

 

[pradyuthal]

Sir, you are great! The solution as you provided proved to be excellent one to solve my problem exactly and accurately.

A lot of thanks to you , sir.