How do I compare two Excel file and add remarks?

Help Activities
, ,
1

I have an excel file(Input) with columns “Vendor Code”,“Vendor Name”,“Vendor Type” & “Vendor Division”. Another file(Master File) has “Name 1” and “Vendor Code”.

I should compare “Vendor Name” and “Name 1”.

If i get a match, then I should check if the “Vendor Type” is Non Trade and the 1st two characters of “Vendor Code” is 74. If it becomes true, remarks should be added as “Duplicate found” in the input file.

If its not true, then I should check if the “Vendor Division” is CED and the 1st two characters of “Vendor Code” is 75. If it becomes true, remarks should be added as “Duplicate found” in the input file.

If its not true, then I should check if the “Vendor Division” is BSD and the 1st two characters of “Vendor Code” is 76.If it becomes true, remarks should be added as “Duplicate found” in the input file.

Vendor_Registration_InputFile.xlsx (11.1 KB)

MasterFile.xlsx (8.5 KB)

Kindly attach the xaml file.

Thank You !

2

@uio
Sequence3.zip (2.1 KB)

image
image1237×791 44.1 KB

' Perform the comparisons and update the Remarks column
For Each inputRow As DataRow In inputTable.Rows
    Dim vendorCode As String = inputRow("ERP Code").ToString()
    Dim vendorName As String = inputRow("Vendor Name").ToString()
    Dim vendorType As String = inputRow("Vendor Type").ToString()
    Dim vendorDivision As String = inputRow("Vendor Division").ToString()

    Dim matchFound As Boolean = False

    ' Check for matching "Vendor Name" and "Name 1" in the master table
    For Each masterRow As DataRow In masterTable.Rows
        If vendorName = masterRow("Name").ToString() Then
            ' Check conditions and update the Remarks column accordingly
            If vendorType = "Non Trade" AndAlso vendorCode.StartsWith("74") Then
                inputRow("Remarks") = "Duplicate found"
                matchFound = True
                Exit For
            ElseIf vendorDivision = "CED" AndAlso vendorCode.StartsWith("75") Then
                inputRow("Remarks") = "Duplicate found"
                matchFound = True
                Exit For
            ElseIf vendorDivision = "BSD" AndAlso vendorCode.StartsWith("76") Then
                inputRow("Remarks") = "Duplicate found"
                matchFound = True
                Exit For
            End If
        End If
    Next

    ' If no conditions matched, clear the Remarks column
    If Not matchFound Then
        inputRow("Remarks") = ""
    End If
Next
3

Hi @uio

Could you give me one confirmation there is no Column called Vendor Name in the Vendor_Registration_InputFile.xlsx and no value in this file is not matching the Name1 column in Master.xlsx file.

Could you check and confirm which column has to take as Vendor Name.

Hope you understand!!

4

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