Compare two Datatable using contains

Help Activities
,
1

How to compare this two excel files using “Ref. No.” column in vendor excel and “Invoice” column in ledger excel and make match mismatch report in two separate excels containing ref no and am
Vendor.xlsx (8.8 KB)
ount from vendor and invoice no and amount from ledger.
Ledger_ax.xlsx (10.6 KB)

1 Like
2

Hi @Debojyoti_chanda

Can you check the below workflow

FIlter.xaml (15.3 KB)

Regards
Gokul

3

Hey @Debojyoti_chanda

Please find the below ref,

nmnithinkrishna_FindMatchMisMatchBetExcel.zip (22.6 KB)

Hope this helps

Thanks
#nK

4

Thanks for your reply. The workflow works fine but i also want the matching data from ledger as well. like one excel containing data from vendor like you did and another for ledger as well in the same way.

1 Like
5

Hey @Debojyoti_chanda

You can please make a copy of the same logic with the data table interchanged in the join and linq assign we have used.

Kindly try and let us know if any difficulties.

Thanks
#nK

6

when i interchange the datatables it inner join the output is this-
Output.xlsx (8.5 KB)
and when i change it to left join the output is like this(works for macthed records)-
Output.xlsx (7.6 KB)
in both cases the linq was this
dt_Ledger.AsEnumerable.Where(Function(row) not dt_Matched.AsEnumerable.Select(Function(obj) obj(“Invoice”).ToString).Distinct().Contains(row(“Invoice”).ToString)).CopyToDataTable.DefaultView.ToTable(true,{“Invoice”,“Amount”})
but gives error in left join

1 Like
7

Okay @Debojyoti_chanda

Let me update it no worries.

But just correct my understanding please, so you want to do the same approach for both the data tables.

Thanks
#nK

8

yes…just like you made a match mismatch using vendor data i want another report containing match mismatch for ledger data

1 Like
9

Clear will update and post it.

Thanks
#nK

1 Like
10

Hey @Debojyoti_chanda

Please find the updated version.

nmnithinkrishna_FindMatchMisMatchBetExcel_v2.zip (22.0 KB)

Thanks
#nK

11

the matched values with “.” are going in mismatch sheet and no mismatch values is getting printed

1 Like
12

Hey @Debojyoti_chanda

Mismatch values getting printed for me…

image
image679×520 9.08 KB

And regarding the dot, could you please remove it if its by mistake or the nature of input is that ?

Thanks
#nK

13

this data is actually matching with vendor and should be in matched sheet

image
image1494×241 17.9 KB

image
image1910×155 9.78 KB

this is not in vendor sheet so should be in mismatch sheet

image
image1406×55 4.3 KB

1 Like