Autofill data from in excel

Copper Contributor



I have 9 column and several rows but i have less value in the same rows. I need to fill the data from R1, R2, R3 if have more then R4. But if R value in first row is blank and I need to fill cell 1 in R1 column by 3 and R2 column by 4. Please check the first photo and change the cell status manually. Please help me because I have a large data sheet. Please send me if any formula have in excel.

6 Replies



You can try this formula. Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel 2021. The formula is in cell K2 and filled across range K2:S14.





I have a large data sheet (how many records does this mean +/- ???) With Power Query (attached):


Thanks for your help and awesome, the formula works for me.
Can you send me the process that you do in Power Query?


HOW TO Create, load, or edit a query in Excel


Edit query TableOut > From the Home tab choose Advanced Editor to see the query code:


    Source = TableIn,
    CombinedValues = Table.AddColumn(Source, "Values", each
        List.RemoveNulls(Record.ToList(_)), type list
    RemovedOtherColumns = Table.SelectColumns(CombinedValues,{"Values"}),
    ExtractedValuesAsString = Table.TransformColumns(RemovedOtherColumns,
        {"Values", each Text.Combine(List.Transform(_, Text.From), ";"), type text}
    SplitColumnByDelimiter = Table.SplitColumn(ExtractedValuesAsString, "Values",
        Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), Table.ColumnNames(Source)
    TypedColumns = Table.TransformColumnTypes(SplitColumnByDelimiter,
        List.Transform(Table.ColumnNames(Source), each {_, type number})


PS: Marking one working option you got as Solution helps people who Search - Thanks



In attached file added a 365 option:



With data in table TableIn:

      TEXTJOIN(";",,BYROW(TableIn, LAMBDA(rw, TEXTJOIN(",",,rw)))),