Autofill data from in excel

[Jayanta85]

 

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.

[#2]

@Jayanta85 

=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.

[#3]

@Jayanta85 

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

[#4]

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

[#5]

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

[#6]

@Jayanta85 

> 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:

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

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

[#7]

@Jayanta85 

In attached file added a 365 option:

With data in table TableIn:

=IFNA(
  VALUE(
    TEXTSPLIT(
      TEXTJOIN(";",,BYROW(TableIn, LAMBDA(rw, TEXTJOIN(",",,rw)))),
      ",",";"
    )
  ),
  ""
)