# 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
Here is an alternative solution with Power Query
Merge the two tables as shown in the attached.

#### Attachments

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