Compare 2 sheets and get the value

Hi,
I have 2 sheets which contains value which need to compare using “.Contains” method.
Region_Sheet1.xlsx (8.8 KB)
Region_BU2.xlsx (8.7 KB)
Region_Sheet1 contains India that needs to be compared with sheet Region_BU2 column Region State India BU.
So we need to compare it using contains method?
Can anyone suggest the approach for the same?

@Kunal_Jain

Try this

Dt2.asenumnerable.where(function(X) X(“columnname”).contains(“india”).CopyToDataTable

If you want any other let me know

Cheers

Hi @Shiva_Nikhil
Both the datatable needs to be compared.
with each other
eg: DT1.columnname(“Region”).contains(“India”) = Dt2.columnname(“Region state”).contains(“India”)
Something Like this expression I need.
Thanks!!

@Kunal_Jain

Dt1.AsEnumerable().Where(Function(row) Dt2.AsEnumerable().Any(Function(x) x(“RegionSate”).ToString.contains(row(“Region”).ToString))).CopyToDataTable

Try this once

2 Likes

dt1.AsEnumerable.Where(Function(x) dt2.AsEnumerable.Any(Function(r)r(ColumnName).ToString.contains(x(ColumnName).ToString))).CopyToDataTable

Hi @Shiva_Nikhil
It is not working for me.
Can you share the workflow please?

@Kunal_Jain

check the below code

Sequence.xaml (8.3 KB)

Hi @Shiva_Nikhil


It is showing Document is invalid

@Kunal_Jain

comparing2files.zip (23.3 KB)

in that go to sequence you will find in that

cheers

Hi @Kunal_Jain ,
You can try for each row in data table
Can you share expect output?

Hi @Nguyen_Van_Luong1
The output is simple
If we get a match in both the sheet just add to data table otherwise send message no match.

You need compare all name and region
that’s right?

Just region
Because Comparison of name wont be possible for the sheets I have provided.
Names might be different.

oh, it’s simple, wait me few minute

ok
Thanks!!
I will wait for your response.

@Kunal_Jain ,


this output is your expect output?
Sequence.xaml (22.7 KB)

@Kunal_Jain

will the code worked for you

have you checked it

Hi @Shiva_Nikhil
I have checked your code.
But I need an If condition for my code.
So that I can give a check where if the condition is true I have to add data row, otherwise throw an error.
Thanks!!


But I need an If condition for my code.
So that I can give a check where if the condition is true I have to add data row, otherwise throw an error.
Thanks!!

@Kunal_Jain

you need to check each row one by one

or you want to check entire table