Forum Discussion

Nibmus's avatar
Nibmus
Copper Contributor
Aug 06, 2022

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
2TRUE0
5TRUE2
6TRUE2
10102
7TRUE2
8TRUE2
442
9TRUE2
11TRUE3
12TRUE3
333
13TRUE3
14TRUE3
15TRUE3

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!

excel

3 Replies

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Aug 09, 2022

    Nibmus 

    Approximately the same as PeterBartholomew1 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.

  • PeterBartholomew1's avatar
    PeterBartholomew1
    Silver Contributor
    Aug 06, 2022

    Nibmus 

    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)))

     

     

  • OliverScheurich's avatar
    OliverScheurich
    Gold Contributor
    Aug 06, 2022

    Nibmus 

    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.

Resources