Copy Value from Next cell Dynamically

[jai_kumar2 (jai kumar)]

Hi I am having a excel file in that some cells are empty so i want to copy above cell value to the below empty cells like image shown below

My output should like this

Any help appreciable, Thank you.

[Krishna_Raj]

@jai_kumar2

• Step 1: Open Excel File
  • Activity: Excel Application Scope
• Step 2: Read Excel Data
  • Activity: Read Range (Output: dtExcelData)
• Step 3: Loop Through Rows
  • Activity: For Each Row (Input: dtExcelData)
• Step 4: Check for Empty Cells
  • Activity: If (Condition: String.IsNullOrEmpty(row("ColumnName").ToString))
• Step 5: Fill Empty Cells
  • Activity: Assign
    • To: row("ColumnName")
    • Value: dtExcelData.Rows(dtExcelData.Rows.IndexOf(row) - 1)("ColumnName")
• Step 6: Write Back to Excel
  • Activity: Write Range (Input: dtExcelData)

[Kartheek_Battu]

Hello @jai_kumar2

Read Range: Excel Read Range activity
Input: Your Excel file path
Output: DataTable (let’s call it dt)

For Each Row: For Each Row activity
Input: dt

Assign:
int columnIndex = dt.Columns.IndexOf(“YourColumnName”) ’ Replace “YourColumnName” with the actual column name

If: If activity
Condition: row(columnIndex).ToString().Trim = “”

Assign:
  row(columnIndex) = row(columnIndex - 1)

Write Range: Excel Write Range activity
Input: dt
SheetName: Your sheet name
Range: “” (empty to write the entire DataTable)

Thanks & Cheers!!!

[mkankatala (Mahesh Kankatala)]

Hi @jai_kumar2

You can use the Vb.net code to copy the value in column value to next cell if it is empty.
→ Use read range workbook activity to read the excel and store in a datatable, make sure to remove add headers. The Output Variable we will call it as inputDataTable.
→ Use the Invoke code activity to execute the code, give the below code in the edit code option,

outputDataTable = inputDataTable.Clone()
 
Dim currentValue As String = ""
 
For Each row As DataRow In inputDataTable.Rows
    If Not String.IsNullOrWhiteSpace(row(0).ToString()) Then
        currentValue = row(0).ToString()
    End If
 
    Dim newRow As DataRow = outputDataTable.Rows.Add()
    newRow(0) = currentValue
    newRow(1) = row(1)
Next

Open the Edit arguments option give it as below,

→ use write range workbook activity to write the outputDataTable Variable to excel.

Check the below workflow for better understanding,

You provided input,

Output,

Hope it helps!!