I have a datatable dt, in which I want to populate the column “Package” in row Volume with the value just above it where the Keyword is Retail.
i/p:
Attaching file for your testing.
Sheet1 is input & Sheet2 is expected output.
test.xlsx (10.2 KB)
Use this in invoke code and send dt as in/out argument
Dt.AsEnumerable.Where(function(x,i) Not (i mod 2 = 0)).ToList.ForEach(sub(r) r(0) = dt.Row(dt.Rows.IndexOf(r)-1)(0).ToString)
Cheers
updatedDt = dt.Clone() ' Create a new DataTable with the same structure as dt
For Each row As DataRow In dt.Rows
Dim newRow As DataRow = updatedDt.Rows.Add(row.ItemArray) ' Add a new row to updatedDt with the same values as row
If newRow("Package") Is DBNull.Value OrElse String.IsNullOrEmpty(newRow("Package").ToString()) Then
Dim index As Integer = dt.Rows.IndexOf(row)
If index > 0 Then
newRow("Package") = dt.Rows(index - 1)("Package")
End If
End If
Next
Hi Anil, getting some errors while using this in invoke code. Like ByRef Dt cannot be used in lambda expression.
Updated you expression to: Dt.AsEnumerable().Where(Function(x, i) Not (i Mod 2 = 0)).ToList().ForEach(Sub(r) r(0) = Dt.Rows(Dt.Rows.IndexOf(r)-1)(0).ToString())
There was “row” error as well.
Although still not working.
Hi, thanks for your reply. If you see the Package G, it also has to populate the cell below replacing the “abc” which is existing value there.
Your requirement is if your column package is empty then you have to copy the above cell data. but after G there is a Data “abc” then there is no need to replace that right.Please correct me.
Can you please tell the error…for the first lambda error you can add one more argument with only in and send same dt again and use that isnide the foreach statement instead of same dt
Cheers
It has to update the value regardless if the volume row is empty or not.
' Assuming dt is your DataTable
' Initialize a variable to store the last "Retail" package value found
Dim lastRetailPackage As String = ""
' Iterate through each row in the DataTable
For Each row As DataRow In dt.Rows
' Check if the current row's Keyword column value is "Retail"
If row("Keyword").ToString().Equals("Retail") Then
' Update the last found "Retail" package value
lastRetailPackage = row("Package").ToString()
ElseIf row("Keyword").ToString().Equals("Volume") Then
' If the current row's Keyword is "Volume", update its Package column with the last found "Retail" package value
row("Package") = lastRetailPackage
End If
Next
Please check this once
' Assuming dt is your DataTable
' Initialize a variable to store the last "Retail" package value found
Dim lastRetailPackage As String = ""
' Iterate through each row in the DataTable
For Each row As DataRow In dt.Rows
' Check if the current row's Keyword column value is "Retail"
If row("Keyword").ToString().Equals("Retail") Then
' Update the last found "Retail" package value
lastRetailPackage = row("Package").ToString()
ElseIf row("Keyword").ToString().Equals("Volume") Then
' If the current row's Keyword is "Volume", update its Package column with the last found "Retail" package value
row("Package") = lastRetailPackage
End If
Next
This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.