Fill the blank cells in excel with the value above

Help Studio
, ,
1

I have a column in excel which can have values or it can be empty. I want to fill the blank cells with the values found in cell above.

Making sure not to fill values beyond total row count of the datatable.
image

output should look like:

image
image490×525 6.78 KB

2

@SunnyJha

Dim previousvalue As String
For Each r As datarow In dt.AsEnumerable
	If r("ColumnName").ToString.Equals("") Or String.IsNullOrEmpty(r("ColumnName").ToString) Then
		r("ColumnName")=previousvalue
		Else
			previousvalue=r("ColumnName").ToString
	End If
	Next

Use this in invoke code activity inplace of ColumnName provide your columnName

image
image1214×524 27.2 KB

1 Like
3

Hi @SunnyJha

You can use the below LinQ Expression to achieve this,

- Assign -> dt_Output = (From row In Input_dt.AsEnumerable()
                         Let rowIndex = Input_dt.Rows.IndexOf(row)
                         Let previousRow = If(rowIndex > 0, Input_dt.Rows(rowIndex - 1), Nothing)
                         Let currentValue = row("YourColumnName").ToString()
                         Let previousValue = If(previousRow IsNot Nothing, previousRow("YourColumnName").ToString(), Nothing)
                         Let newValue = If(String.IsNullOrEmpty(currentValue), previousValue, currentValue)
                         Select row("YourColumnName") = newValue).CopyToDataTable()

Change the Column names based on your excel.

Hope it helps!!

4

An Approach with essentials:

1 Like
5

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.