 New Contributor

# How to have a list of numbers that skip when it gets to certain values?

I want to make a list of numbers. The thing is, some slots have already been filled by other numbers using a formula.

 10 4 3

I want to be able to populate the spaces in between in order, while skipping the values that have already been inputted, like so:

 1 2 5 6 10 7 8 4 9 11 12 3 13 14 15

How do I do this? Been trying everything I knew all day, but I can't seem to find a fully functional solution. I have a table that looks like this:

 1 TRUE 0 2 TRUE 0 5 TRUE 2 6 TRUE 2 10 10 2 7 TRUE 2 8 TRUE 2 4 4 2 9 TRUE 2 11 TRUE 3 12 TRUE 3 3 3 3 13 TRUE 3 14 TRUE 3 15 TRUE 3

The second column is used for inserting the specific numbers in specific places, and its TRUE values are counted for the "in between numbers". It's also the basis for what numbers the first column should skip. The third column counts how many numbers have already been skipped. Its value is added to the numbers in the first column.

It kind of works, but really buggy. Any help would be appreciated!

3 Replies

# Re: How to have a list of numbers that skip when it gets to certain values?

``````Sub fill()

Dim start_variant As Variant
Dim bereich As Range
Dim result_variant As Variant
Dim i As Long
Dim k As Long
Dim number As Long
Dim rows_result_variant As Long

Range("C:C").Clear

Set bereich = Range("A1:A7500")
start_variant = bereich

rows_result_variant = Application.WorksheetFunction.CountA(bereich)

ReDim Preserve start_variant(1 To 7500, 1 To 1)

ReDim result_variant(1 To rows_result_variant, 1)
k = 1
For i = 1 To 7500
If Cells(i, 1) <> "" Then
result_variant(k, 1) = Cells(i, 1).Value
k = k + 1

Else
End If

Next i

number = 1
For i = 1 To 7500

If Cells(i, 1) = "" Then

For k = 1 To rows_result_variant

If result_variant(k, 1) = number Then
number = number + 1
k = 0

Else
End If

Next k
start_variant(i, 1) = number
number = number + 1

Else
End If

Next i

Range("C1:C7500") = start_variant

End Sub``````

Maybe with this code. In the attached file you can enter unique numbers in range A1:A7500. Then you can click the button in cell E2 to run the code.

# Re: How to have a list of numbers that skip when it gets to certain values?

A 365 solution.

``````= LET(
k, SEQUENCE(ROWS(Formula)),
missing, FILTER(k, ISERROR(XMATCH(k, Formula))),
pointer, SCAN(0, Formula, LAMBDA(a,f, IF(f, a, a+1))),
IF(Formula, Formula, INDEX(missing,pointer)))
``````

# Re: How to have a list of numbers that skip when it gets to certain values?

Approximately the same as @Peter Bartholomew solution, but works on less modern Excel

``````=IFERROR(
IF( B3 = "",
AGGREGATE(15,6, (ROW(Formula)-ROW(\$B\$2)) /
ISERROR( MATCH(ROW(Formula) - ROW(\$G\$2), Formula, 0 ) ),
ROW() - ROW(\$G\$2) - COUNTA(\$B\$3:B3) ),
B3 ),
"" )``````

Above in G3 and drag down till empty cells appear.