Fill BLANKs down, up or both in column(s) - similar to Power Query

Filler =LAMBDA(table,LET( header, TAKE(table, 1), data, DROP(table, 1), counter, SEQUENCE(COLUMNS(data)), FillDown, LAMBDA(a, v, IF(v = "", a, v)), Complete, LAMBDA(vector, LET(ini, TAKE(TOCOL(vector, 1), 1), SCAN(ini, vector, FillDown))), Stack, DROP(REDUCE("", counter, LAMBDA(a, v, HSTACK(a, Complete(INDEX(data, , v))))), , 1), VSTACK(header, Stack) ))

SergeiBaklan

Diamond Contributor

Nov 29, 2023

Lorenzo

Interesting, thank you. Yes, good enough for most practical cases.

Lorenzo

Silver Contributor

Nov 29, 2023

SergeiBaklan

And timing obviously varies between tables. I took a different random one still 300k * 7 cols to fill Down then Up:

- PQ (firewall off): 6sec

- FillColumns I posted earlier: 13+sec

Also compared, with same table, 3 options:

// By @Patrick2788: 5sec
=LAMBDA(table,
  LET(
    header,   TAKE(table, 1),
    data,     DROP(table, 1),
    counter,  SEQUENCE(COLUMNS(data)),
    FillDown, LAMBDA(a, v, IF(v = "", a, v)),
    Complete, LAMBDA(vector, LET(ini, TAKE(TOCOL(vector, 1), 1), SCAN(ini, vector, FillDown))),
    Stack,    DROP(REDUCE("",counter, LAMBDA(a,v, HSTACK(a,Complete(INDEX(data,,v))))),,1),
    VSTACK(header, Stack)
  )
)

// From thread 'Can SCAN() and BYROW() be combined?': 6sec
=LAMBDA(array,
LET(
    fnFill, LAMBDA(array,
      LET(
          reverse,  LAMBDA(arr, SORTBY(arr, SEQUENCE(ROWS(arr)), -1)),
          down,     LAMBDA(arr, SCAN(, arr, LAMBDA(a, v, IF(v = "", a, v)))),
          reverse(down(reverse(down(array))))
      )
    ),
    fillData, LAMBDA(arr,
      REDUCE(
          1,
          SEQUENCE(COLUMNS(arr)),
          LAMBDA(A, i, IF(SEQUENCE(,i) = i, fnFill(INDEX(arr,, i )), A))
      )
    ),
    VSTACK(TAKE(array,1), fillData(DROP(array,1)) )
  )
)

// From FillColums as a separate LAMBDA: 5sec
// with @Patrick2788 packaging
=LAMBDA(table,
  LET(
    header,     TAKE(table, 1),
    data,       DROP(table, 1),
    counter,    SEQUENCE(COLUMNS(data)),
    FirstValue, LAMBDA(arr, IF(TAKE(arr,1)="",XLOOKUP(TRUE,arr<>"",arr,""),"")),  
    Fill,       LAMBDA(seed,v, IF(v="",seed,v)),  
    FillCol,    LAMBDA(arr, SCAN(FirstValue(arr),arr,Fill)),
    Stack,      DROP(REDUCE("",counter, LAMBDA(a,v, HSTACK(a,FillCol(INDEX(data,,v))))),,1),
    VSTACK(header, Stack)
  )
)

djclements

Bronze Contributor

Nov 29, 2023

Lorenzo Very nice display of various dynamic array techniques here. Personally, I still prefer VBA for filling down. It requires very little code, is highly efficient, and can be easily adapted to fit any scenario (ie: excluding certain rows or filling down based on values in another column). The basic fill down code for your original screenshot of Table2 would be something like this:

Sub FillDown()

    Dim rg As Range, arr As Variant, i As Long, j As Long
    Set rg = Sheet1.ListObjects("Table2").DataBodyRange
    arr = rg.Value

    For i = 2 To UBound(arr, 1)
        For j = 1 To UBound(arr, 2)
            If arr(i, j) = vbNullString Then arr(i, j) = arr(i - 1, j)
        Next j
    Next i

    rg.Value = arr

End Sub

I've never ran into a scenario where I also needed to fill up; however, that can be done by simply reversing the direction of the first loop and filling values from the row below:

Sub FillDownUp()

    Dim rg As Range, arr As Variant, i As Long, j As Long
    Set rg = Sheet1.ListObjects("Table2").DataBodyRange
    arr = rg.Value

'Fill down
    For i = 2 To UBound(arr, 1)
        For j = 1 To UBound(arr, 2)
            If arr(i, j) = vbNullString Then arr(i, j) = arr(i - 1, j)
        Next j
    Next i

'Fill up
    For i = UBound(arr, 1) - 1 To 1 Step -1
        For j = 1 To UBound(arr, 2)
            If arr(i, j) = vbNullString Then arr(i, j) = arr(i + 1, j)
        Next j
    Next i

    rg.Value = arr

End Sub

When tested with 520,000 rows x 5 columns of data, it completed in 4.25 seconds; with 1,040,000 rows of data, it took 8.5 seconds.

Forum Discussion

Fill BLANKs down, up or both in column(s) - similar to Power Query

Resources