Forum Discussion

Lorenzo's avatar
Lorenzo
Silver Contributor
Nov 27, 2023
Solved

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

Hi   Looked at +/- replicating Fill Down and Fill Up functionnalities in Power Query with a LAMBDA. My attempt below:           // FillDownUp: =LAMBDA(array, LET( Data, DRO...
excel
Excel for web
Excel on Mac
office 365
  • Patrick2788's avatar
    Patrick2788
    Nov 27, 2023

    Lorenzo 

    My variant:

     

    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's avatar
SergeiBaklan
MVP
Nov 27, 2023

Lorenzo 

Looks nice. My variant for FillCol

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

More correct name will be Fill Down and Up (not fill up and down)

  • Lorenzo's avatar
    Lorenzo
    Silver Contributor
    Nov 28, 2023

    SergeiBaklan 

    More correct name will be Fill Down and Up (not fill up and down) Named it UpDown as in reality this is what my approach does. Though, this is quite confusing ==> Initial post revised

    • Lorenzo's avatar
      Lorenzo
      Silver Contributor
      Nov 28, 2023

      Thank you SergeiBaklan & Patrick2788 

       

      What I had in mind (wasn't sure that was realistic) was to provide a single LAMBDA (to allow copy/paste in Name Manager) for cases like (but not only):

       

       

      where, for whatever reason Power Query cannot be used. Added a couple of optional parameters:

       

       

      I don't post the formula as it's a monster now. It's available in the attached workbook - seems to work decently... I stop there but will wait a couple of days before closing this thread in case you have feedback/recommendation (I'm gonna change the title of the discussion accordingly)

      • SergeiBaklan's avatar
        SergeiBaklan
        MVP
        Nov 28, 2023

        Lorenzo 

        I like this formula. Sure that could be other variants, as for everything in Excel (and not only), but the only reason to revise the formula could be the performance, if it works fine on hundreds thousands of rows. On the other hand such is not practical for the majority of Excel cases.

        It works fine and do it function, thank you for sharing.

Resources