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

 

[tboulden]

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

LET makes it not-quite-so-ugly, massaged your solution a little so that it will work.

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

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)
G2:G46	G2	=LET( begin,A2:A4, end,B2:B4, rangeSize,end-begin+1, dim,ROWS(begin), seq_r,SEQUENCE(dim), seq_c,SEQUENCE(,dim), low_triangle_matrix,IF(seq_r>seq_c,1,0), offset,MMULT(low_triangle_matrix,rangeSize), start,begin-offset, result_seq,SEQUENCE(SUM(rangeSize),,0), result_offset,INDEX(start,MATCH(result_seq,offset,1)), result,result_seq+result_offset, result )
Dynamic array formulas.

 

[tboulden]

A formula solution might be possible with LAMBDA, but I can't seem to figure it out.

The LET can easily be converted to a LAMBDA.

Book1
	A	B	C	D	E	F	G
1	Start	End	Offset	Range size	Start	Result	LAMBDA
2	42	46	0	5	42	42	42
3	99	102	5	4	94	43	43
4	55	90	9	36	46	44	44
5	120	1111	45	992	75	45	45
6	66	99	1037	34	-971	46	46
7						99	99
8						100	100
9						101	101
10						102	102
11						55	55
12						56	56
13						57	57
14						58	58
15						59	59
16						60	60
Sheet2

Cell Formulas
Range	Formula
C2:C6	C2	=SUM(C1:D1)
D2:D6	D2	=B2-A2+1
E2:E6	E2	=A2-C2
F2:F1072	F2	=SEQUENCE(SUM(D:D),,0)+VLOOKUP(SEQUENCE(SUM(D:D),,0),C2:E6,3)
G2:G1072	G2	=LAMBDA(begin_rng,end_rng, LET( begin,begin_rng, end,end_rng, rangeSize,end-begin+1, dim,ROWS(begin), seq_r,SEQUENCE(dim), seq_c,SEQUENCE(,dim), low_triangle_matrix,IF(seq_r>seq_c,1,0), offset,MMULT(low_triangle_matrix,rangeSize), start,begin-offset, result_seq,SEQUENCE(SUM(rangeSize),,0), result_offset,INDEX(start,MATCH(result_seq,offset,1)), result,result_seq+result_offset, result ) )(A2:A6,B2:B6)
Dynamic array formulas.

 

[Peter_SSs]

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

FWIW I don't think the s array is required.

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