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

Help Studio
, ,
1

Hello Everyone,

I have the following scenario:
Excel1
Requestor name : John
Category : light, Internet
Total : 20,36 , 15,54

Excel2
Employee Name : John
Light :
Internet :

I need to search for the requestor’s name in Excel2 under the ‘Employee Name’ column and place the corresponding total values in two different columns: ‘Light’ and ‘Internet’.
Excel 1

image
image565×111 2.53 KB

Excel 2
image
Output Excel2
image

2

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

image
image951×512 21.4 KB

image
image505×183 3.38 KB

3

how can we do this without code?

4

Hi @HELZMOTH_BJ ,

Please find the below xmal file
Testing_Forum (2).zip (742.8 KB)

try this xmal file and let me know if you are facing any issues

5

Hi @HELZMOTH_BJ

Check this:
Sequence.xaml (26.8 KB)

6

Actually i forget to tell you that if
Requestor name : John Bell ,then in employee name : value can be John or Bell or John Bell. value can be either full name or firstname or lastname

7

@HELZMOTH_BJ

Try this:
Sequence.xaml (27.6 KB)

8

Try to use contains in filter data table activity

1 Like
9

Hey but i have 2 excel file so how can i use that in use excel file activity

10

@HELZMOTH_BJ

follow the steps

  1. read excel 1 into datatable dt
  2. use excel file activity and give the second excel
  3. use for each row in excel with required sheet
  4. inside loop use filter datatable on dt with currentrow("Enployee Name").ToString and use contains in column Reqestor Name
  5. Now use lookup datatable and lookup for light and internet and fill the value using assign …currentrow("Light") = value from lookup for lgiht and similarly for internet

cheers

11

in filter data table use contains