• 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

  • excelhelpdt30122022.xlsx
    12.9 KB · Views: 9
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

  • excelhelpdt30122022.xlsx
    17.5 KB · Views: 7
Dear Sir,
Thanks, can you please tell me, how can I learn to prepare this reconciliation any video or guide can you please share?
 
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.
 
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.
 
Back
Top