• 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.

comparing two excel sheets.

Shabbo

Member
Dear Sir,
I have two sheets 1) Bank Statement 2) Bank Ledger
My data is not in a same format I wanted to reconcile both sheet and highlight the matching cells with color yellow amount difference with up to Rs 1 can be ignored.
I have been trying to solve this type of problem since years because my data is large and comes in as per attached sheet format only.
Does excel have any solution for the same?
 

Attachments

AVK

Active Member
One way trying. For reconcile any one field common required.
Hence extract number from description & narration column from both sheet. With formula
=IF(SUM(LEN(D2)-LEN(SUBSTITUTE(D2, {"0","1","2","3","4","5","6","7","8","9"}, "")))>0, SUMPRODUCT(MID(0&D2, LARGE(INDEX(ISNUMBER(--MID(D2,ROW(INDIRECT("$1:$"&LEN(D2))),1))* ROW(INDIRECT("$1:$"&LEN(D2))),0), ROW(INDIRECT("$1:$"&LEN(D2))))+1,1) * 10^ROW(INDIRECT("$1:$"&LEN(D2)))/10),"")

and with index match formula match the record
=IFERROR(INDEX('Bank Statement'!E:E,MATCH('Bank Ledger'!$G2,'Bank Statement'!$H:$H,0))," ")

See the attach file.
Yellow highlighted not match.
 

Attachments

Shabbo

Member
Dear Sir,
Thanks, can you please tell me, how can I learn to prepare this reconciliation any video or guide can you please share?
 

Shabbo

Member
One way trying. For reconcile any one field common required.
Hence extract number from description & narration column from both sheet. With formula
=IF(SUM(LEN(D2)-LEN(SUBSTITUTE(D2, {"0","1","2","3","4","5","6","7","8","9"}, "")))>0, SUMPRODUCT(MID(0&D2, LARGE(INDEX(ISNUMBER(--MID(D2,ROW(INDIRECT("$1:$"&LEN(D2))),1))* ROW(INDIRECT("$1:$"&LEN(D2))),0), ROW(INDIRECT("$1:$"&LEN(D2))))+1,1) * 10^ROW(INDIRECT("$1:$"&LEN(D2)))/10),"")

and with index match formula match the record
=IFERROR(INDEX('Bank Statement'!E:E,MATCH('Bank Ledger'!$G2,'Bank Statement'!$H:$H,0))," ")

See the attach file.
Yellow highlighted not match.
Dear Sir,
Its great formula, I could not even think about such a big formula for solution, where can I understand how this formula is written because for me to practicing this formula some guidelines required, I can pay consultancy if required to understand this formula.
 

AlanSidman

Well-Known Member
Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

- Follow this link to learn how to install Power Query in Excel 2010 / 2013.

- Follow this link for an introduction to Power Query functionality.

- Follow this link for a video which demonstrates how to use Power Query code provided.
 

shili12

Member
Herewith is a solution for you for your bank recon a continuation from above thread, apparently everything checks out except for :-


ERPCASHBOOKBank StatementDec2022BOTH
AmountCount Of
Credit
Sum Of
Credit
Count Of
Debit
Sum Of
Debit
Frequency difference
65,189.02
0​
-
1​
65,189.02- 1
65,189.07
1​
65,189.07
0​
- 1
 

Attachments

shili12

Member
@AlanSidman
I was just going thru your xlsx and made some observations:-
(i) 2140 is reflected x 4 in your solution, but in both bank statement and ledger it appears x 2 and cancels itself out.
(ii) the following does not appear in your merge in spite its one of the recon differences.
02-Nov-22​
02-Nov-22​
BRV2022110130customer--06547892
65189.07

The overall totals for both bank statement 291,068.05 and bank ledger 291,068.10 do not tie up with your merge which shows totals of 286,788.05 and 221,599.03
 

Attachments

Top