Linq copy row that match to another sheet

Help Activities
, ,
1

I have 3 file (input , master , report)

I want to check condition as below.

  1. Check file input column (SR Category,SR Sub Category,SR Issue,SR Product Line) that match in file master
    1.1 Match check column SR # and column Activity # from file input with file report
    1.1.1 If found check column Closed Date file report have text or not
    1.1.1.1 If text = “working” or blank ----> go to next row file input
    1.1.1.2 If text <> “working” but have value ----> copy this row from file input to sheet Correct
    1.2 Match check column SR # and column Activity # from file input with file report
    1.2.1 If found check column Closed Date file report have text or not
    1.2.1.1 If text = “working” or blank ----> go to check input next row
    1.2.1.2 If text <> “working” but have value ----> copy this row from file input to sheet Correct
    1.3 Not match copy row to sheet incorrect.

Please guide me for solve it. ( I want use switch case or LINQ)

Report.xlsx (492.1 KB)
Master.xlsx (12.8 KB)
input1.xlsx (10.1 KB)

2

@fairymemay

Please try as below:

Assuming inputDt, masterDt, and reportDt are DataTable variables containing data from the input, master, and report files respectively

Step 1: Define LINQ query to filter and manipulate data

Dim correctRows = (From inputRow In inputDt.AsEnumerable()
Join masterRow In masterDt.AsEnumerable()
On New With {Key.SRCategory = inputRow(“SR Category”),
Key.SRSubCategory = inputRow(“SR Sub Category”),
Key.SRIssue = inputRow(“SR Issue”),
Key.SRProductLine = inputRow(“SR Product Line”)}
Equals New With {Key.SRCategory = masterRow(“SR Category”),
Key.SRSubCategory = masterRow(“SR Sub Category”),
Key.SRIssue = masterRow(“SR Issue”),
Key.SRProductLine = masterRow(“SR Product Line”)}
Join reportRow In reportDt.AsEnumerable()
On New With {Key.SRNumber = inputRow(“SR #”),
Key.ActivityNumber = inputRow(“Activity #”)}
Equals New With {Key.SRNumber = reportRow(“SR #”),
Key.ActivityNumber = reportRow(“Activity #”)}
Where Not (reportRow(“Closed Date”).ToString().Trim() = “working” Or String.IsNullOrEmpty(reportRow(“Closed Date”).ToString().Trim()))
Select inputRow).CopyToDataTable()

Dim incorrectRows = (From inputRow In inputDt.AsEnumerable()
Where Not masterDt.AsEnumerable().Any(Function(masterRow)
Return inputRow(“SR Category”).ToString() = masterRow(“SR Category”).ToString() AndAlso
inputRow(“SR Sub Category”).ToString() = masterRow(“SR Sub Category”).ToString() AndAlso
inputRow(“SR Issue”).ToString() = masterRow(“SR Issue”).ToString() AndAlso
inputRow(“SR Product Line”).ToString() = masterRow(“SR Product Line”).ToString()
End Function)
Select inputRow).CopyToDataTable()

’ Step 2: Write Correct and Incorrect data to Excel sheets
correctRows.WriteRange(“CorrectSheet”, “A1”)
incorrectRows.WriteRange(“IncorrectSheet”, “A1”)

1 Like
3

@naveen.s It sheet correct.
I would like to add one more condition to the previous one as below.

  1. After check condition as below (Sheet Correct).
    Check file input column SR # ,If found dup stamp dup in column in N file input

How to edit your code?

Thank you.