Compare 2 different excel sheets with different headers

Hi Team,
1st image shows the input data of Settlement date, Amount , Account No . Now i need to compare the data with another sheet .2nd image shows the data which need to be validated.
Settlement date = posting date(if date is not found i need to look into + - 2 days , even if not found 2days i need to look into + - 5 days), Amount = Original amount, Account no = Account No and Method should be check as always. Can anyone help me in this case i need to use only linq


Summary

This text will be hidden

@smarthari1997

First connect your excel as db using oledb drivers… and database activities

Now use select * From [SheetName] to get all details from each table…

Then you have to loop theough the first datatable and then filter the data in dt2 using filter datatable …

Or even instead of filter datatable…

In the select query of second sheet we can include where clasue to filter the data based on dates

Cheers

This query might work for just comparing the dates & other columns
SELECT t1., t2.
FROM table1 t1
JOIN table2 t2
ON t1.SettlementDate = t2.postingdate AND t1.amount = t2.Originalamount AND t1.accountno = t2.accountno AND AND t1.method = ‘Post’ AND t2.method = ‘Post’

or to check for ±2 or ±5 days gap you can use ‘Interval’

SELECT t1., t2.
FROM table1 t1
JOIN table2 t2
ON (t1.SettlementDate = t2.postingDate OR t1.Settlementdate BETWEEN t2.postingDate - INTERVAL 2 DAY AND t2.postingDate + INTERVAL 2 DAY OR t1.SettlementDate BETWEEN t2.postingDate - INTERVAL 5 DAY AND t2.postingDate + INTERVAL 5 DAY )

if possible …can u make it in linq?

Can you try this
from t1 in dt1
from t2 in dt2
where (t1.SettlementDate == t2.PostingDate ||
(t1.SettlementDate >= t2.PostingDate.AddDays(-2) && t1.SettlementDate <= t2.PostingDate.AddDays(2)) ||
(t1.SettlementDate >= t2.PostingDate.AddDays(-5) && t1.SettlementDate <= t2.PostingDate.AddDays(5)))
select new { t1, t2 };

Try this sample & modify according to your filters

P.S : I have just filtered on Account Num, date1=date2 or date1 in range of ±2 in date2 , you can add additional logic to that linq acc to your requirement.
LinQ.zip (15.9 KB)

1 Like