LookUp with two table with name Difference

Help
,
1

I have some issues completing “Lookup and Filtering Data” task.

I want to get the average value of sheet2 and put it in sheet1.

The name of sheet 2 is full name and the name of sheet 1 is abbreviation.

filter data.png
filter data.png696×253 6.55 KB

LA Dasers Player.xlsx (11.0 KB)

2

Hello @111132

You can use the below steps to get this done…

  • Read sheet 1 into a separate data table using read range
  • Read sheet 2 into another data table using read range
  • Use a For Each Row activity first to loop through the sheet 1
  • Inside the for each row activity, place another For Each Row activity to loop through the other datatable that holds Sheet 2 data.
    • Why I have sheet 1 first is because, we have to check whether player of sheet 1 is available in the player column of sheet 2. We cannot do this other way because sheet 2 contains the full name and sheet 1 contains the first which will not really match. However, a disadvantage of having this types of checks is, if you have the same name for two players, there is no way to figure which one is which one.
  • Inside the second loop, place a IF activity to check the condition

Sheet2Row("Player").ToString.Contains(Sheet1Row("Player").ToString))

  • In the true part of the condition, have an assign activity to assign the average of sheet two to sheet 1 data table

Sheet1Row("Average") = Sheet2Row("Average").ToString

Now after all that,

  • Place a write range activity to write the data of the datatable 1 that contains sheet 1 data back to the excel sheet. This will update both player and average columns with the exact data.

Hope it helps

Excellence is achieved through constant challenging and breaking off from your limitations. It isn’t taught or given; it begins within you. – @Lahiru.Fernando

3

Thank you for your advise.
But I haven’t solve this problem according to your advise.

I upload file.
Please check and give me more advise.

“As Advisd” is as your advise.
The other two is my own jobs( using Lookup Activity)

  • My job include Open browser, /
  • This project is like this,
    I would like to know player’s batting average of the Dodgers this year who run today’s game.
    Sheet 1 is today’s player and Sheet 2 is the batting average for the Dodgers team this year.
    (The name of players are written in Korean)

Hope your advise.
Thank you again

Main.xaml (72.2 KB) .