Linq help to check

Help Studio
1

could you help me with one more query where i need to check the combination of parent and subacc from sheet 1 and if that combination is there then we can add match else mismatch like in output sheet but data to be populated on sheet1 column D
outputdata.xlsx (8.4 KB)
input.xlsx (9.3 KB)

2

Hi @manoj2500

You can try this query
dt1.AsEnumerable().ToList().ForEach(
Sub(r)
r(“Result”) = If(dt2.AsEnumerable().
Any(Function(x) x(“ParentAcc”).ToString = r(“ParentAcc”).ToString _
AndAlso x(“SubAcc”).ToString = r(“SubAcc”).ToString),
“Match”, “Mismatch”)
End Sub)
Cheers

3

Hi @manoj2500

Use this code in invoke code and map arguments as shown.
Excel Manipulations.xaml (9.8 KB)

’ === Normalize function inline (lambda style) ===
Dim normalize = Function(val As Object)
If val Is Nothing Then Return “”
Return val.ToString().Trim().Replace(" “, “”).Replace(”_", “”).ToUpper()
End Function

’ === Create lookup from dt2 ===
Dim lookup As New HashSet(Of String)()

For Each r2 As DataRow In dt2.Rows
Dim key As String = normalize(r2(parent2Col)) & “|” & normalize(r2(sub2Col))
lookup.Add(key)
Next

’ === Add Result column if missing ===
If Not dt1.Columns.Contains(“Result”) Then
dt1.Columns.Add(“Result”)
End If

’ === Compare dt1 rows ===
For Each r1 As DataRow In dt1.Rows
Dim key As String = normalize(r1(parent1Col)) & “|” & normalize(r1(sub1Col))

If lookup.Contains(key) Then
    r1("Result") = "Match"
Else
    r1("Result") = "Mismatch"
End If

Next
’

image
image770Ă—334 28.1 KB

Let me know if you face any issues :slight_smile:

4 Likes
4

Put this formula in Sheet1 cell D2 (adjust sheet/column names if needed) and copy down:

=IF(COUNTIFS(Output!$A:$A,A2,Output!$B:$B,B2)>0,“match”,“mismatch”)

Here Output is the sheet that contains valid Parent (column A) and SubAcc (column B) pairs, and Sheet1 has Parent in A and SubAcc in B. Replace column letters or sheet name to match your workbook.

1 Like
5

You cane first read input file both sheets read dt1 and dt2 below linq use

VariableName = dt2.AsEnumerable().
Select(Function(r) r(“Parent”).ToString.Trim & “|” & r(“SubAcc”).ToString.Trim).ToList()

dt1.AsEnumerable().ToList().
ForEach(Sub(r) r(“D”) = If(VariableName.Contains(r(“Parent”).ToString.Trim & “|” & r(“SubAcc”).ToString.Trim), “Match”, “Mismatch”))

1 Like
6

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