Dear Sir,
I wanted to do the receipt reconciliation; my Sheet1 has Amount in Column E and bank reference in column F and my receipt sheet has ledger.
I wanted to reconcile the both transaction like
1) If Transaction available in Sheet1 and Receipt then Sheet 1 Column G should show as “Yes” and amount from Column “H”
2) If transaction available in sheet1 receipt then receipt sheet column H should show Yes and if not available in then show as NO.
This only partially works because of the lack of uniformity between the way information is placed in column F of Sheet1 and also the differences between what is in column F of Sheet1 and column G of the Receipt sheet.
This [partial] proposal addresses query #1 and employs a helper column (I) which may be moved and/or hidden for aesthetic purposes.
The helper column is populated using: =IF(F8="","",RIGHT(F8,LEN(F8)-SEARCH("/",SUBSTITUTE(F8,"-","/",LEN(F8)-LEN(SUBSTITUTE(F8,"-",""))))))
The formula to find the amount is: =IFERROR(IF(F8="","",INDEX(Receipt!J$7:J$863,MATCH(I8,Receipt!G$7:G$863,0))),0)
The formula for entered is: =IF(F8="","",IF(H8=E8,"Yes","Not"))
Let us know if you have any questions.
Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks