How to match the data from 2 different excel worksheets?

Hi Everyone! I am now working on a project which drives me to compare and match some figures between worksheets in the same workbook. I have watched some Youtube videos teaching about using “For each row” twice to separate as 2 datatables. But since my database is kind of large, are there any better ways for me to match the data in a more efficient way?

@kit2815 Hello I would like to help you.

There are another way to make a match, could you provide me the files and a file or picture about what are the results that you are expecting.

it is depending on some more details, but a common concept is to do it with data joins

  • join datatable activity
  • LINQ script

With some more details we can check for alternates as well

Hi @Manuel_Dominguez

Thanks for your help.

Attached is the excel file for my project testing.
Output_testing.xlsx (17.8 KB)

I would like to compare the “ID” column between the two sheets called “Staff” and “OEE”. If they are matched, I would like to populate the matched results with the details of “OEE”. For example, we could observe 2 IDs are identical between the two worksheets. So, I hope these 2 rows of “OEE” information including Date, Name and Post be populated in a new sheet.

Hi @ppr

Thanks for your warm suggestion.

I am not sure whether my case is applicable to data joins, but I could give it a try.

So, what I undertand is that you want this result.

And if this is True, what will be the value for column POST?

has a high potential to get it solved

  • with Left Join / Inner Join - depending on the requirements
  • LINQ

as Manuel already asked the fetched information from the match are to specify in a more precise definition

Hi @Manuel_Dominguez

Yes, this is the result I want. Really thanks for your help.

For Post, it will be all fine to leave it blank first.

Could you share the xaml file with me?

Hi @ppr

I have tried the file with the method of “Inner Join” and it also works.

Many thanks for your suggestion indeed.

Perfect,

the removing from the unneeded Columns we can do with the filter datatable activity with the filter options related to the datacolumns.

Anything else on where we can support?

Nothing else to seek for your advice currently.

But perhaps we could wait for @Manuel_Dominguez to have a look at whether there is another better alternative or not. :smile:

Perfect, feel free to explore in the meanwhile a LINQ Approach like this
InnerJoin_1Col_Result_AllLeft-1Right.xaml (10.7 KB)

And get starter help info here:

Hahah In Fact I used JOIN like @ppr suggested, I just wanted to be sure about the result expected.

Sure. Thanks for your sharing.

On the other hand, may I ask does the join datatable activity enable the functionality of “OR” in case I want to match more than 1 column between the sheets?

No worries. Thanks for your help @Manuel_Dominguez :smile:

handling two cols for join condition we can do:

  • when 2 cols are both to look - anonymous object approach, combining the two condition approach
  • when 2 cols are to look in an OR way - custom LINQ, maybe another approach, where the mactch is not done with a join condition

Hi @ppr

Would you kindly provide me with the custom LINQ file where 2 cols are to look in an OR way please if you have time?

@kit2815
as a demo have a look here:
grafik

we are using the where approach due to the join syntax of LINQ has constraints and we cannot apply.


(From d1 In dt1.AsEnumerable
Let jr = dt2.AsEnumerable.Where(Function (d2) d1(0).toString.Trim.Equals(d2(0).toString.Trim) Or d1(1).toString.Trim.Equals(d2(1).toString.Trim))
From j In jr
Let ra = d1.ItemArray.Concat(j.itemArray).toArray
Select r = dtCode.Rows.Add(ra)).CopyToDataTable

find starter help here:
InnerJoin_2Cols_OR-Condition.xaml (11.2 KB)

Edited:
with following rewriting to an ANY we can reduce the redundand code for the different columns

(From d1 In dt1.AsEnumerable
Let jr = dt2.AsEnumerable.Where(Function (d2) {0,1}.Any(Function (x) d1(x).toString.Trim.Equals(d2(x).toString.Trim)))
From j In jr
Let ra = d1.ItemArray.Concat(j.itemArray).toArray
Select r = dtCode.Rows.Add(ra)).CopyToDataTable

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