I have an excel on SP with sheet1 data and sheet2
what i want to do is from sheet1 col G value to picked and the check in sheet 2 col B if found then populate the col M with same value found under Col C of sheet 2 against col B value and Col N should be populated with Match /Mismatch as per data in Col J and Col M
Could you please help
input.xlsx (9.5 KB)
outputdata.xlsx (8.6 KB)
Hi
Read both sheets → use VLOOKUP logic → update M & N.
Use Excel formulas:
M: =IFERROR(VLOOKUP(G2,Sheet2!B:C,2,FALSE),“”)
N: =IF(J2=M2,“Match”,“Mismatch”)
Turn Sheet2 into a dictionary:
dict = dt2.AsEnumerable.ToDictionary(Function(r) r(“B”).ToString, Function(r) r(“C”).ToString)
Then use for each row in sheet
row(“M”) = If(dict.ContainsKey(row(“G”).ToString), dict(row(“G”).ToString), “”)
row(“N”) = If(row(“J”).ToString = row(“M”).ToString, “Match”, “Mismatch”)
Cheers
@nishiijain2000 any we can use anything like vb code or something
@manoj2500 yes you can do this easily with VB code inside assigns
Hi @manoj2500
If this work for you please mark as solve
Hi @manoj2500
Using Vb.net its working, Can you check.
Sequence.xaml (9.3 KB)
@MohammedShabbir i am getting invocation error could you please share me for the updates column names
input.xlsx (9.5 KB)
I updated code in this, the problem here is your headers are changing. You can map on the columns if the position will is not gonna change in future.
Let me know if this works for you.
Sequence.xaml (9.0 KB)
input.xlsx (9.3 KB)
outputdata.xlsx (8.4 KB)
@MohammedShabbir 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
Hi @manoj2500
You can check the Parent + SubAcc combination by using a LINQ query and then update the result in Sheet1 Column D.
Read both sheet into datatable then use for each row as datatable
If dtSheet2.AsEnumerable().Any(Function(r) r(“Parent”).ToString.Trim = row(“Parent”).ToString.Trim AndAlso
r(“SubAcc”).ToString.Trim = row(“SubAcc”).ToString.Trim) Then
row(“Result”) = “Match”
Else
row(“Result”) = “Mismatch”
End If
Then write the data into excel
Hope this will solve your problem
If this solution worked plz mark as solved
Cheers
You can use this linq query
dtSheet1.AsEnumerable().ToList().ForEach(Sub(r)
r(“Result”) = If(dtSheet2.AsEnumerable().
Any(Function(x) x(“Parent”).ToString = r(“Parent”).ToString AndAlso
x(“SubAcc”).ToString = r(“SubAcc”).ToString),
“Match”, “Mismatch”)
End Sub)
this is giving multiple error @nishiijain2000 like end statement
Hi @manoj2500
Made this generic, you can change column names in aruguments, if its chaanging going forward.
’
’ Arguments:
’ dt1 (In/Out), dt2 (In/Out) as DataTable
’ parentCol1, subCol1, qtyCol1 (In) As String
’ parentCol2, subCol2, qtyCol2 (In) As String
For Each r1 As DataRow In dt1.Rows
Dim p1 As String = r1(parentCol1).ToString().Trim()
Dim s1 As String = r1(subCol1).ToString().Trim()
' Default if no match found
Dim qty2Value As Object = Nothing
For Each r2 As DataRow In dt2.Rows
Dim p2 As String = r2(parentCol2).ToString().Trim()
Dim s2 As String = r2(subCol2).ToString().Trim()
If String.Equals(p1, p2, StringComparison.OrdinalIgnoreCase) AndAlso
String.Equals(s1, s2, StringComparison.OrdinalIgnoreCase) Then
qty2Value = r2(qtyCol2)
Exit For
End If
Next
' Add or update column in dt1 for Quantity2
If Not dt1.Columns.Contains("Quantity2") Then
dt1.Columns.Add("Quantity2", GetType(Object))
End If
r1("Quantity2") = qty2Value
Next
’
If this design helped you fix the problem, please mark as solution so it can help others too
Hi @MohammedShabbir
is soltion for this thread
seems some confusion your previous query was help ful and worked
This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.
