Autofill data from in excel

Question

&nbsp;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.

oliverscheurich · Answer

Jayanta85&nbsp;=IFERROR(INDEX($A2:$I2,SMALL(IF(NOT(ISBLANK($A2:$I2)),COLUMN($A:$I)),COLUMN(A:A))),"")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.&nbsp;&nbsp;

lorenzo · Answer

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

jayanta85 · Answer

Thanks for your help and awesome, the formula works for me.

jayanta85 · Answer

Can you send me the process that you do in Power Query?

lorenzo · Answer

Jayanta85&nbsp;&gt;&nbsp;HOW TO Create, load, or edit a query in Excel&nbsp;Edit query&nbsp;TableOut &gt; From the Home tab choose Advanced Editor to see the query code:&nbsp;let
    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})
    )
in
    TypedColumns&nbsp;PS: Marking one working option you got as Solution helps people who Search - Thanks&nbsp;

Forum Discussion

Autofill data from in excel

6 Replies

Resources