Aug 06 2022 01:27 AM - edited Aug 06 2022 01:44 AM
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!
Aug 06 2022 06:40 AM
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.
Aug 06 2022 08:06 AM
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)))
Aug 09 2022 03:36 AM
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.