Incremental numbers in one cell || Office 2016

[arjun5ne]

Dear Team,

I need help on excel formula how to display incremental numbers in one cell.

Example: 7 increment i.e. 7 14 21 28 35 42 49 56 63 70 - - - - - -

but in one cell display till 49 number only i.e. i need to use CONCATENATE till 49

 

[arjun5ne]

please find image for reference

 

[kevin9999]

There may be native Excel functions that can achieve this (I'm unaware of any) but if you're willing to entertain a UDF, the following should give you what you want. When you enter the Steps() formula, enter the <start number> or select a cell containing the value, <upper limit> or select a cell containing the value, <step> or select a cell containing the value. So using your sample data, and this code:

Function Steps(StartNo As Long, EndNo As Long, Jump As Long) As String
    Dim x As Long, s As String
    Do While x <= EndNo - Jump
        If s = "" Then
            s = StartNo
            x = StartNo
        Else
            x = x + Jump
            s = s & " " & x
        End If
    Loop
    Steps = s
End Function

Produces this result:

Book1
	D	E	F	G	H	I	J	K	L	M
1
2	105		7 14 21 28 35 42 49 56 63 70 77 84 91 98 105
3	161		7 14 21 28 35 42 49 56 63 70 77 84 91 98 105 112 119 126 133 140 147 154 161
4
Sheet1

Cell Formulas
Range	Formula
F2:F3	F2	=Steps(7,D2,7)

 

[arjun5ne]

Hi Kevin thanks for your response.

Can we not do this without VBA code in excel 2016

 

[kevin9999]

Hi Kevin thanks for your response.

Can we not do this without VBA code in excel 2016

Possibly, and hopefully someone will come forward with a non-VBA solution.

 

[Anonymous1378]

Concatenation of a range of cells in Excel 2016 and before is limited. You could use UDFs, like the one suggested, or power query. If you are looking for a native formula that does not require you to reference each cell in your range one by one, I believe you are out of luck. I'm going to assume you're not looking for a wall of text like

=CONCATENATE(IF($B$2<=$D2,$B$2&" ",""),IF($B$3<=$D2,$B$3&" ",""),IF($B$4<=$D2,$B$4&" ",""),IF($B$5<=$D2,$B$5&" ",""),IF($B$6<=$D2,$B$6&" ",""),IF($B$7<=$D2,$B$7&" ",""),IF($B$8<=$D2,$B$8&" ",""),IF($B$9<=$D2,$B$9&" ",""),IF($B$10<=$D2,$B$10&" ",""),IF($B$11<=$D2,$B$11&" ",""),IF($B$12<=$D2,$B$12&" ",""),IF($B$13<=$D2,$B$13&" ",""),IF($B$14<=$D2,$B$14&" ",""),IF($B$15<=$D2,$B$15&" ",""),IF($B$16<=$D2,$B$16&" ",""),IF($B$17<=$D2,$B$17&" ",""),IF($B$18<=$D2,$B$18&" ",""),IF($B$19<=$D2,$B$19&" ",""),IF($B$20<=$D2,$B$20&" ",""),IF($B$21<=$D2,$B$21&" ",""),IF($B$22<=$D2,$B$22&" ",""),IF($B$23<=$D2,$B$23&" ",""),IF($B$24<=$D2,$B$24&" ",""),IF($B$25<=$D2,$B$25&" ",""),IF($B$26<=$D2,$B$26&" ",""),IF($B$27<=$D2,$B$27&" ",""),IF($B$28<=$D2,$B$28&" ",""),IF($B$29<=$D2,$B$29&" ",""),IF($B$30<=$D2,$B$30&" ",""),IF($B$31<=$D2,$B$31&" ",""),IF($B$32<=$D2,$B$32&" ",""))

 

[Peter_SSs]

Welcome to the MrExcel board!

On a side-issue:

in excel 2016

Is your profile information correct?

Can we use column B to create another helper column?

23 07 24.xlsm
	B	C	D	E
1
2	7	7	105	7 14 21 28 35 42 49 56 63 70 77 84 91 98 105
3	14	7 14	161	7 14 21 28 35 42 49 56 63 70 77 84 91 98 105 112 119 126 133 140 147 154 161
4	21	7 14 21
5	28	7 14 21 28
6	35	7 14 21 28 35
7	42	7 14 21 28 35 42
8	49	7 14 21 28 35 42 49
9	56	7 14 21 28 35 42 49 56
10	63	7 14 21 28 35 42 49 56 63
11	70	7 14 21 28 35 42 49 56 63 70
12	77	7 14 21 28 35 42 49 56 63 70 77
13	84	7 14 21 28 35 42 49 56 63 70 77 84
14	91	7 14 21 28 35 42 49 56 63 70 77 84 91
15	98	7 14 21 28 35 42 49 56 63 70 77 84 91 98
16	105	7 14 21 28 35 42 49 56 63 70 77 84 91 98 105
17	112	7 14 21 28 35 42 49 56 63 70 77 84 91 98 105 112
18	119	7 14 21 28 35 42 49 56 63 70 77 84 91 98 105 112 119
19	126	7 14 21 28 35 42 49 56 63 70 77 84 91 98 105 112 119 126
20	133	7 14 21 28 35 42 49 56 63 70 77 84 91 98 105 112 119 126 133
21	140	7 14 21 28 35 42 49 56 63 70 77 84 91 98 105 112 119 126 133 140
22	147	7 14 21 28 35 42 49 56 63 70 77 84 91 98 105 112 119 126 133 140 147
23	154	7 14 21 28 35 42 49 56 63 70 77 84 91 98 105 112 119 126 133 140 147 154
24	161	7 14 21 28 35 42 49 56 63 70 77 84 91 98 105 112 119 126 133 140 147 154 161
25	168	7 14 21 28 35 42 49 56 63 70 77 84 91 98 105 112 119 126 133 140 147 154 161 168
concatenate

Cell Formulas
Range	Formula
E2:E3	E2	=INDEX(C:C,MATCH(D2,B:B))
C2:C25	C2	=IF(C1="","",C1&" ")&B2

 

[Rick Rothstein]

Function Steps(StartNo As Long, EndNo As Long, Jump As Long) As String
    Dim x As Long, s As String
    Do While x <= EndNo - Jump
        If s = "" Then
            s = StartNo
            x = StartNo
        Else
            x = x + Jump
            s = s & " " & x
        End If
    Loop
    Steps = s
End Function

A little more compact...

Function Steps(StartNo As Long, EndNo As Long, Jump As Long) As String
  Dim X As Long, S As String
  For X = StartNo To EndNo Step Jump
    S = S & " " & X
  Next
  Steps = Trim(S)
End Function

 

[arjun5ne]

thankyou all for valuable guidance

 

[Peter_SSs]

You're welcome. Hope you got something that was useful.

BTW, what is the issue regarding versions?

Also BTW, another UDF option

Function Steps(StartNo As Long, EndNo As Long, Jump As Long) As String
  Steps = Join(Filter(Application.Transpose(Evaluate(Replace(Replace("if(mod(row(#:^)-#," & Jump & ")=0,row(#:^),""x"")", "#", StartNo), "^", EndNo))), "x", False))
End Function