Dear Madam,
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 ?
Hi Sarfraz,
You can use Text.Select to extract the number IDs from the two statements, as explained here: Extract Letters, Numbers, Symbols from Strings in Power Query with Text.Select and Text.Remove • My Online Training Hub
Then you can merge the two queries using a full outer join matching the Number ID columns and Debit and Credit columns. Any rows that don't match will having empty data on one side and any matching rows will display the data from the other table.
See example attached. Note: this is not without limitations. It doesn't match the dates because not all transactions are posted in the ledger on the data listed in the bank statement. It also assumes the number IDs are unique per transaction in a statement period.
It cannot ignore small differences. I hope that helps. Alternatively, you might find this tutorial on bank reconciliations helpful.
Mynda
Dear Madam,
Wish you happy new year.
some one has helped me with writing excel formula for comparing two sheets, can you please help me write this kind of formula for working in excel.
Hi Sarfraz,
Happy new year to you too!
I'm not sure what you're asking for as the file you attached already has the formula working in Excel.
Mynda
Dear Madam,
I am asking the formula which is there in my excel sheet is not written by me, I wanted to understand logic behind this formula and I also I wanted to learn this formula.
How can you help me?
Hi Sarfraz,
That formula is woefully inefficient. You can use this simpler formula nowadays:
=TEXTJOIN("",TRUE,IFERROR((MID(D2,ROW(INDIRECT("1:"&LEN(D2))),1)*1),""))
That said, I wouldn't use this formula either because it's still inefficient because it uses INDIRECT for one, plus the Power Query solution I gave you is still the best solution.
The above formula uses LEN to get the text length in the cell D2. It then feeds that to the ROW function via INDIRECT to return an array of numbers which MID uses to extract the numbers from the cells that are then joined together again with TEXTJOIN.
Use the Evaluate Formula tool on the Formulas tab to step through the formula evaluating to further understand it.
Mynda
Dear Madam,
your power query solution works but its littlie bit difficult me to understand full process how you reconciled.
I could understand how to extract text but didn't understand merge two queries using a full outer matching the number ID columns and debit and credit columns.
how can you help more to understand full process ?
"Hi Sarfraz,
You can use Text.Select to extract the number IDs from the two statements, as explained here: Extract Letters, Numbers, Symbols from Strings in Power Query with Text.Select and Text.Remove • My Online Training Hub
Then you can merge the two queries using a full outer join matching the Number ID columns and Debit and Credit columns. Any rows that don't match will having empty data on one side and any matching rows will display the data from the other table.
See example attached. Note: this is not without limitations. It doesn't match the dates because not all transactions are posted in the ledger on the data listed in the bank statement. It also assumes the number IDs are unique per transaction in a statement period.
It cannot ignore small differences."
Hi Sarfraz,
Follow the link for the tutorial on Text.Select. Then see tutorial 3.05 on merging queries.
Mynda