Forum Discussion

anupambit1797's avatar
anupambit1797
Iron Contributor
Jan 24, 2024
Solved

Sequence Issue

Dear Experts,

                       Greetings!

I have an issue like below, where exported a list from txt file and it has report like below:-

 

Now, the empty cells, should be such that after 4 should be all 4, till it reaches the next already filled cell, and if that cell has say another value say 5, then populate the remaining cells after 5 as all 5, and so on.

Please if we can achieve via "No Coding"

 

Thanks in Advance,

Br,

Anupam

excel
Formulas and Functions
  • Lorenzo's avatar
    Lorenzo
    Silver Contributor
    Jan 24, 2024

    Hi anupambit1797 

     

    I have an issue like below, where exported a list from txt file

    From past cases you're used to use Power Query. Why not importing the text file via Power Query and use the built-in Fill Down function on your column?

     

    Within PQ Editor: Right-click on the column > Fill > Down > Close & Load

  • PeterBartholomew1's avatar
    PeterBartholomew1
    Silver Contributor
    Jan 24, 2024

    anupambit1797 

    There are two possibilities.  The first is that you are using 365 and the other that you should be using 365.  To fill a value down requires the formula

     

    = SCAN("", status, LAMBDA(x,y, IF(y, y, x)))

     

    placed in the first cell of the fill down sequence,

    Better still is to define a Lambda function so that your solution becomes

     

    "Worksheet formula"
= Filldownλ(status)

"Definition" Filldownλ
=SCAN("", s, LAMBDA(x, y, IF(y, y, x)))

    While I think of it, PowerQuery also has the ability to perform FillDown tasks and can be used with Excel 2010 and onwards.

     

Resources