Compare 2 different excel sheets with different headers

Help Studio
, ,
#1

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

IMG_20230123_123518
IMG_20230123_1235181920×1442 102 KB

2
21920×1080 236 KB

Summary

This text will be hidden

#2

@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

#3

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 )

#4

if possible …can u make it in linq?

#5

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 };

#7

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