• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

2 files -compare costs for same date and same person ID

Hi
I have 2 file
In the excel file 1 and file 2
I just want to see where for the same date, there is the same expense in both file.
This analysis should be performed the same Employee ID.
Basically, only the 1st row should give a positive.
 

Attachments

  • 2 files.xls
    45.5 KB · Views: 3
can you help me, since i don't know how to do with two variables, both empoyeeid, date, and amount, can you give an exampple.
 
Hi Narunfca,

Please see the attached file.

The formula below works great - but now I see that I need to add an additional factor, to check if the date in file 2 is within the date range in file 1 - and then to return a reply of matched or not.


=IFERROR(IF(F3=INDEX('File 2 '!$C$2:$C$16,MATCH(A3&TEXT(C3,"dd-mm-yyyy"),'File 2 '!$A$2:$A$16&TEXT('File 2 '!$B$2:$B$16,"DD-MM-YYYY"),0)),"MATCHED","NOT MATCHED"),"NOT FOUND")

There are 3 rows in the example, For the test purpose , 2 should provide a positive result and 1 should give no match.

Thank you
 

Attachments

  • Date Range.xls
    45 KB · Views: 3
Hi

Enclosed the file. Alter few data to check.

with best regards

Arun

Do you like the formula or any additional requirements?
 

Attachments

  • Date Range.xls
    47.5 KB · Views: 5
that is amazing. the fomulae is great.

I just noticed one thing, that that the amount to look for is not always the same in both tabs (when i ran for real)

Can, we add a tolerance of amount to check of above or below 10%?

thanks
 
also what do you mean
'with helper column' and 'without helper column'
what is the the difference between the two
thanks
 
Hi:

Does this non array formula do what you are looking for?

=IFERROR(IF(ABS(F2-INDEX('File 2 '!$C$2:$C$16,MATCH(1,MMULT((A2='File 2 '!$A$2:$A$16)*(C2='File 2 '!$B$2:$B$16),1),0)))/F2<0.1,"Matched","Not Found"),"Not Found")

Thanks
 
Hi:

Does this non array formula do what you are looking for?

=IFERROR(IF(ABS(F2-INDEX('File 2 '!$C$2:$C$16,MATCH(1,MMULT((A2='File 2 '!$A$2:$A$16)*(C2='File 2 '!$B$2:$B$16),1),0)))/F2<0.1,"Matched","Not Found"),"Not Found")

Thanks
A small change, instead of first "Not found", it should be "Not Matched".
The formula will be

=IFERROR(IF(ABS(F2-INDEX('File 2 '!$C$2:$C$16,MATCH(1,MMULT((A2='File 2 '!$A$2:$A$16)*(C2='File 2 '!$B$2:$B$16),1),0)))/F2<0.1,"Matched","Not Matched"),"Not Found")

with best regards
Arun N
 
Back
Top