Fill in missing sequential numbers using two value in separate columns

[bchi99]

I have a random two-column sheet that I need to list the values between in a single column. The output given the following two columns below should be shown below as well. Anyone with vba knowledge help?

I NEED THIS:

42
43
44
45
46
99
100
102
55
56
57
.
.
.
90
120
121
123
.
.
.
1111
66
67
68
.
.
.
99

Current file is comprised of this original values

42	46
99	102
55	90
120	1111
66	99

 

[DRSteele]

This can be done with the new function SEQUENCE. Do you have Excel 365? Your signature in your profile has a place where you can tell others what Excel version you are using.

 

[bchi99]

i have excel 365

 

[Fluff]

i have excel 365

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[bchi99]

i dont' think sequence can take in values already in a sheet, maybe I'm wrong but to I need A1=42, B1=46, A2=55, B2=57 to be:

42
43
44
45
46
55
56
57

 

[bchi99]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

thanks just did that

 

[Fluff]

It would be better is you update your profile, rather than put it in your signature.

 

[DRSteele]

I might have spoken too soon. It's troublesome to create an array inside SEQUENCE. I can do it per single row, but even then I can't stack the resulting array. PQ or VBA might be necessary. A formula solution might be possible with LAMBDA, but I can't seem to figure it out.

 

[Fluff]

Assuming your values are in A2 & b2 downwards, how about

Sub bchi()
   Dim Ary As Variant, Nary As Variant, s As Variant
   Dim r As Long, i As Long, nr As Long
   
   Ary = Range("A2:B" & Range("A" & Rows.Count).End(xlUp).Row).Value2
   ReDim Nary(1 To Rows.Count - 2, 1 To 1)
   For r = 1 To UBound(Ary)
      s = Evaluate("row(" & Ary(r, 1) & ":" & Ary(r, 2) & ")")
      For i = 1 To UBound(s)
         nr = nr + 1
         Nary(nr, 1) = s(i, 1)
      Next i
   Next r
   Range("E2").Resize(nr).Value = Nary
End Sub

 

[Eric W]

It can be done with formulas, if you use a few helper columns:

Book1
	A	B	C	D	E	F
1	Start	End	Offset	Range size	Start	Result
2	42	46	0	5	42	42
3	99	102	5	4	94	43
4	55	90	9	36	46	44
5						45
6						46
7						99
8						100
9						101
10						102
11						55
12						56
13						57
14						58
15						59
16						60
Sheet4

Cell Formulas
Range	Formula
C2:C4	C2	=SUM(C1:D1)
D2:D4	D2	=B2-A2+1
E2:E4	E2	=A2-C2
F2:F46	F2	=SEQUENCE(SUM(D:D),,0)+VLOOKUP(SEQUENCE(SUM(D:D),,0),C2:E4,3)
Dynamic array formulas.

I think it would be possible to include the helper columns within the formula, but it would be really ugly.