Populate or overwrite data in excel based on some condition

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:


op:

Attaching file for your testing.
Sheet1 is input & Sheet2 is expected output.
test.xlsx (10.2 KB)

@SunnyJha

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

@SunnyJha

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


Output:

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.

@SunnyJha

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.

@SunnyJha

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.

@SunnyJha

' 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


Output

Please check this once

@SunnyJha

' 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


Output

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