Forum Discussion
FILL lambda: Fill blank cells in Multiple direction (↓↑→←) across 2D arrays
Hey everyone!!
Sharing something I've been putting together: a Lambda for filling blank cells across ranges, with full control over direction and proper 2D array support.
=FILL(array, [directions])Clean single-function interface. The [directions] argument takes:
- "d" -> Down (default when omitted)
- "u" -> Up
- "r" -> Right
- "l" -> Left
Want to chain multiple fills? Just pass an array constant, each direction runs in sequence, output of one feeding into the next:
=FILL(A1:D20, {"r","d"})Super handy for tables with both row and column headers that got merged.
Five Lambdas under the hood. Only FILL is meant to be called directly, the rest are internal, prefixed with Dep_ to make that clear:
| Name | What it does |
|---|---|
| FILL | Call this one, handles all 4 directions |
| Dep_Fill_D_or_U | Vertical fill engine |
| Dep_Fill_R_or_L | Horizontal fill engine |
| Dep_VMirror | Flips row order -> enables fill-up |
| Dep_HMirror | Flips column order -> enables fill-left |
Blank cells and empty strings are treated the same way throughout.
Drop any questions or feedback below, and if you find a use case for it, I'd love to hear about it!
All functions available on my GitHub https://gist.github.com/Medohh2120/14697b32ff089aa559d26547a5bd82a9
2 Replies
It would better if you share a sample file with few use cases so that visitor can download the file and understand the use of custom function.
Thank you for sharing. not sure when I might use but neat none the less.
Could also improve the description because at least for me I wasn't clear on what it would do. Basically it will take a range and spit that range back out but any blanks or "" will be 'filled' based on the last available value to the left,right,up, or down so a fill right will take the last value to the left of a series of blanks and fill that value to the right.
That said, you have a unknown function name in your FILL function 'clean_arr'
Not sure what that was/is supposed to do but I replaced is with 'array' and it seems to work in the cases I tried
In trying some things it is interesting if you have an #N/A Error value it will 'skip' that cell and that cell becomes blank instead of the error. but if you have multiple directions then it will get filled in on the second direction. Other errors like #NAME? and #DIV/0! are treated like values (and maybe that is what 'clean_arr' was intended to address?
