Linq_Filter_DataTable with Update row Items

[Balachander_Pandian]

Hi Expert,
I have an excel file with contains FROM Gst no , To GST No and Remarks Column. If both From Gst no and To Gst no columns value are same then remarks column will come as TRUE. If both values are differ then Remarks column will come as False. Then, need to separate both False and True details in the same sheet with given once space row. Attached the input file and expected output in the output sheet in the same input file.

how can i achieve this by linq ?
Sample_Input.xlsx (10.5 KB)

[vrdabberu (Varunraj Dabberu)]

Hi @Balachander_Pandian

Sub SeparateDataBasedOnCondition()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1")
    
    Dim lastRow As Long
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    
    Dim trueRows As Range
    Dim falseRows As Range
    Dim i As Long
    
    
    For i = 2 To lastRow
        If ws.Cells(i, 1).Value = ws.Cells(i, 2).Value Then
            If trueRows Is Nothing Then
                Set trueRows = ws.Rows(i)
            Else
                Set trueRows = Union(trueRows, ws.Rows(i))
            End If
            ws.Cells(i, 3).Value = "TRUE"
        Else
            If falseRows Is Nothing Then
                Set falseRows = ws.Rows(i)
            Else
                Set falseRows = Union(falseRows, ws.Rows(i))
            End If
            ws.Cells(i, 3).Value = "FALSE"
        End If
    Next i
    
    
    If Not trueRows Is Nothing Then
        trueRows.Copy
        ws.Rows(lastRow + 2).Insert Shift:=xlDown
        Application.CutCopyMode = False
    End If
    
    
    If Not falseRows Is Nothing Then
        falseRows.Copy
        ws.Rows(lastRow + IIf(trueRows Is Nothing, 1, 3)).Insert Shift:=xlDown
        Application.CutCopyMode = False
    End If
    
    
    Set ws = Nothing
    Set trueRows = Nothing
    Set falseRows = Nothing
End Sub

Regards

[bavyaravu133]

@Balachander_Pandian

• Read the data from the Excel file.
• Compare the From Gst no and To Gst no columns.
• Add the comparison result to the Remarks column.
• Separate the rows based on the Remarks column value.
• Write the result back to the Excel file.

[Anil_G (Anil Gorthi)]

@Balachander_Pandian

follow the steps

1. Read data into datatable dt
2. Use the following in assign to populate remarks columns(Assumption is its already present if not then add the column first) dt.Columns("Remarks").Expression = "IIF([GST From Column]=[GST To Column],'True','False')"
3. Use assign with dt.AsEnumerable.Where(function(x) x("Remarks").ToString.Equals("True")).Concat({dt.NewRow}).Concat(dt.AsEnumerable.Where(function(x) x("Remarks").ToString.Equals("False"))).CopyToDataTable
4. Write the data to excel excel

cheers

[Balachander_Pandian]

Hi @Anil_G Thank you very much