Search for value in another Excel and place the total value in 2 different column corresponding to that value

[supriya117 (Supriya Allada)]

Hi @HELZMOTH_BJ

Try this code:

For Each excel1Row As DataRow In dt_Excel1.Rows
    Dim matchedRow As DataRow = dt_Excel2.AsEnumerable().Where(Function(r) r("Employee Name").ToString().Trim().ToLower() = excel1Row("Requestor name").ToString().Trim().ToLower()).FirstOrDefault()

    If matchedRow IsNot Nothing Then
        Dim totalValues As String() = excel1Row("Total").ToString().Split(","c)
        If totalValues.Length > 0 Then
            If excel1Row("Category").ToString().Contains("Light", StringComparison.OrdinalIgnoreCase) Then
                For Each value As String In totalValues
                    If Not String.IsNullOrEmpty(matchedRow("Light").ToString()) Then
                        matchedRow("Light") = matchedRow("Light").ToString() & ", " & value.Trim()
                    Else
                        matchedRow("Light") = value.Trim()
                    End If
                Next
            End If

            If excel1Row("Category").ToString().Contains("Internet", StringComparison.OrdinalIgnoreCase) Then
                For Each value As String In totalValues
                    If Not String.IsNullOrEmpty(matchedRow("Internet").ToString()) Then
                        matchedRow("Internet") = matchedRow("Internet").ToString() & ", " & value.Trim()
                    Else
                        matchedRow("Internet") = value.Trim()
                    End If
                Next
            End If
        End If
    End If
Next