Create number series in excel

[Shweta]

Hi All,

Please suggest how to create the following series in excel 2007

1
2
2
3
3
3
4
4
4
4
5
5
5
5
5... so on

Thanks in advance!

Regards,
Shweta

 

[VoG]

With a macro

Sub test()
Dim i As Long
For i = 1 To 10
    Range("A" & Rows.Count).End(xlUp).Offset(1).Resize(i).Value = i
Next i
End Sub

 

[AdamL]

For example, if 1 is entered in A1, then in A2:

=A1+(COUNTIF(A$1:A1;A1)=A1)

filled down.

 

[hurgadion]

Hi,
I propose VBA Solution with a double Loop:

Sub test()
Dim i&, j&, x&

For i = 1 To 10
  For j = 1 To i
     x = x + 1
     Cells(x, 1).Value = i
   Next j
Next i
End Sub

Best regards.

 

[Shweta]

Thanks Vog and AdamL!

 

[hurgadion]

Another, shorter Solution:

Sub test2()
Dim i&

For i = 1 To 55
  Cells(i, 2).Value = Int(1 / 2 * (Sqr(8 * i - 7) - 1) + 1)
Next i
End Sub

Of course, VoG's Solution is more useful... Best regards.

 

[Shweta]

Thanks hurgadion!

 

[chetanmehra1989]

Hi All,

Please suggest how to create the following series in excel 2007

1
2
2
3
3
3
4
4
4
4
5
5
5
5
5... so on


if G1 =1
and G2 .

=G2+(COUNTIF(G$2:G2,G2)=G2)
111111111111111111111111111