Generate Datatable from text

I have this excel data : -
image
Apart from Column C and Column F rest is coming from generate dt from text
i want to fill Column C as 5,39 till 23,39 and Column F as 5,80 till 23,80
if I want to search whether datatable contains SOF then it should retrieve me as Y and 05,39 if it contains DKC then Y and 05,80 , basically it should check on both the columns (A and D)
Linq for fetching multiple columns will be needed i think

@Pogboom
Assign activity (use Invoke Code or Assign activity)

Dim dataTable As DataTable = YourDataTable ’ Replace YourDataTable with your actual DataTable variable

’ Loop through each row in the DataTable
For Each row As DataRow In dataTable.Rows
’ Check if the DataTable contains “SOF” in the specified columns
If row(“ColumnNameContainingSOF”).ToString().Contains(“SOF”) Then
’ Fill Column C with 5,39
row(“ColumnC”) = “5,39”
End If

' Check if the DataTable contains "DKC" in the specified columns
If row("ColumnNameContainingDKC").ToString().Contains("DKC") Then
    ' Fill Column F with 5,80
    row("ColumnF") = "5,80"
End If

how it will increment it in both columns its in the format of (row,column) only row should be incremented

@Pogboom
Please try as below:

Assuming dt is your DataTable containing the data

Step 1: Define the values to search for
Dim searchValues As New List(Of String) From {“SOF”, “DKC”}

Step 2: Use LINQ to search for the values in columns A and D
Dim resultRows = From row In dt.AsEnumerable()
Where searchValues.Contains(row.Field(Of String)(“A”)) OrElse searchValues.Contains(row.Field(Of String)(“D”))
Select row

Step 3: Update columns C and F based on the search result
For Each row In resultRows
If row.Field(Of String)(“A”) = “SOF” Then
row.SetField(“C”, “5,39”)
ElseIf row.Field(Of String)(“A”) = “DKC” Then
row.SetField(“C”, “5,80”)
ElseIf row.Field(Of String)(“D”) = “SOF” Then
row.SetField(“F”, “5,39”)
ElseIf row.Field(Of String)(“D”) = “DKC” Then
row.SetField(“F”, “5,80”)
End If
Next

Step 4: Convert the result back to a DataTable if needed
Dim updatedDt As DataTable = resultRows.CopyToDataTable()

SOF and DKC will come as dynamic values , and i have more rows of data so set field should keep incrementing till 23,39 and 23,80

Try this:

Step 1: Define the dynamic values to search for
Dim searchValues As New List(Of String) From {“SOF”, “DKC”}

Step 2: Use LINQ to search for the dynamic values in columns A and D
Dim resultRows = From row In dt.AsEnumerable()
Where searchValues.Contains(row.Field(Of String)(“A”)) OrElse searchValues.Contains(row.Field(Of String)(“D”))
Select row

Step 3: Update columns C and F based on the search result
For Each row In resultRows
Dim rowIndex = dt.Rows.IndexOf(row) ’ Get the index of the current row
Dim baseValue As Integer = 5 ’ Base value for row numbers
Dim incrementValue As Integer = 1 ’ Increment value for row numbers
Dim columnCValue = $“{baseValue + rowIndex * incrementValue},39” ’ Calculate the value for column C
Dim columnFValue = $“{baseValue + rowIndex * incrementValue},80” ’ Calculate the value for column F
row.SetField(“C”, columnCValue) ’ Set the value for column C
row.SetField(“F”, columnFValue) ’ Set the value for column F
Next

Step 4: Convert the result back to a DataTable if needed
Dim updatedDt As DataTable = resultRows.CopyToDataTable()

this is normal correct ? no need to use vb code? since you have mentioned Dim everywhere

This is sufficient for your requirement.

what with the $ sign what will be the variable type of it and also didnt understood row.setfield where to declare it
can you share any workflow if possibile