This comes once in a while in our business and when it does it really is like looking for a needle in a haystack. I can almost (almost) say that I can't wait for the next time so that I can try this out. Thank you very much for your generosity in sharing your knowledge!
Thank you again... It's so very cool to see yet another practical use of pivot tables. With just a few modest, mostly tactical, differences, I have been using pivot tables to reconcile everything with 2 or more "conflicting sources of truth" very similarly for years. Wish I'd seen your video before I demonstrated this technique at a seminar last week. Cheers!
Great tip, Jonathan! I don't believe the Values filter works on a Difference From field. The only workarounds I know of are with Power Pivot, which I didn't want to introduce in this video. I also wanted to retain the ability to add fields to the rows area for further investigation, so that is why I didn't add a column outside the pivot table.
Thanks John. To sort any column you just right click and select Sort. Double click on a cell to see the details would also help (drill down). very practical video 👍
Great summary. I know it is a matter of preference, but we usually do [DR]-[CR] so the CR numbers are always negative. Also, you can filter by Value to get rid of all zeros before you start investigating with dates.
Right, but it doesn't work on a Difference from column because there are blank values in one of the columns. Sorry, I should have explained this in the video and why I used the workaround with sorting.
Great suggestions! 🙌 There are definitely a lot of ways to approach the data preparation for this. I kept it simple with some manual methods that should be approachable and available to most users. In the future we can do a follow up video on more advanced/modern techniques to automate the process.
Thanks Geert! The filter gets weird when adding additional fields to the Rows area (date, description, etc.). The grouped rows will still be hidden by the filter. It would be nice if pivot tables had some improved functionality for this type of filtering on the Difference From field in the Values area. I admit that the sorting technique I show in the video is a bit of a hack.
Great question Siddhant! There are a few ways to go about it. One relatively simple way is to first create a summary report of the bank statement that has one row per transaction with the sum of all values that would match the account. This can be done with a pivot table or Power Query. Then use that report as the source of the bank statement in the table where you combine both sources. There are other ways, but this is probably the easiest. I hope that helps. Thanks again and have a nice day! 🙂
Thanks for the suggestion, Richard! I actually just recorded a video on cleaning up the bank register from QB and we're planning to post it next week. It covers both manual and automated solutions. Stay tuned. 🙂
This is a really smart approach to an often tedious but essential responsibility of every accountant. Thanks for passing it along.
This comes once in a while in our business and when it does it really is like looking for a needle in a haystack. I can almost (almost) say that I can't wait for the next time so that I can try this out. Thank you very much for your generosity in sharing your knowledge!
Thanks so much, Gary! I'm happy to hear you'll be making use of it in the future.
Like always, clear and right to the point
Thank you again... It's so very cool to see yet another practical use of pivot tables. With just a few modest, mostly tactical, differences, I have been using pivot tables to reconcile everything with 2 or more "conflicting sources of truth" very similarly for years. Wish I'd seen your video before I demonstrated this technique at a seminar last week. Cheers!
Thanks so much and I'm happy to hear you're teaching it to others. Save the world some time with recons. 🙌
This is so useful, Jon. Thanks for your generosity in sharing your knowledge!
Thanks so much, Rouel! I'm happy to hear you found it useful. 😊
I have used Avery similar technique many times. I typically filter the row labels based upon the difference column not being equal to zero.
Great tip, Jonathan! I don't believe the Values filter works on a Difference From field. The only workarounds I know of are with Power Pivot, which I didn't want to introduce in this video. I also wanted to retain the ability to add fields to the rows area for further investigation, so that is why I didn't add a column outside the pivot table.
@JonathanExcels Great!
I have several years of data in two registers in QB that I meed to export to Excel!
This is so helpful, thanks Jon👍
Thanks John. To sort any column you just right click and select Sort. Double click on a cell to see the details would also help (drill down). very practical video 👍
Great tip! I should have remembered that one. 🤦♂️ Thanks for sharing, Bart! 🙌
This just answered a year long question for me. Thank you
Thanks Femi! I'm happy to hear that. 🙏
Great summary. I know it is a matter of preference, but we usually do [DR]-[CR] so the CR numbers are always negative. Also, you can filter by Value to get rid of all zeros before you start investigating with dates.
Thanks! Great tip on the credits and debits calculation.
I don't believe you can filter for Values on a Difference From calculation.
@@ExcelCampus Filtering by Values: In the pivot table click on Row Labels, Select Value Filters, choose how you want to filter.
Right, but it doesn't work on a Difference from column because there are blank values in one of the columns. Sorry, I should have explained this in the video and why I used the workaround with sorting.
thank's ! this is solving something that just happen to me !
I would either do this with VSTACK and CHOOSECOLS instead of copy and paste or - even better - Power Query.
Great suggestions! 🙌 There are definitely a lot of ways to approach the data preparation for this. I kept it simple with some manual methods that should be approachable and available to most users. In the future we can do a follow up video on more advanced/modern techniques to automate the process.
Wow, this is awesome
This was a really good and useful tutorial 👏🙏👌
Glad you liked it! 😀
Hey Jon, I would activate the filter buttons in the report area of the Pivot Table and filter out the zeros, we don’t need those.
Thanks Geert! The filter gets weird when adding additional fields to the Rows area (date, description, etc.). The grouped rows will still be hidden by the filter. It would be nice if pivot tables had some improved functionality for this type of filtering on the Difference From field in the Values area. I admit that the sorting technique I show in the video is a bit of a hack.
or, after you paste the 2nd data, you filter the date decreasing and you highlight duplicate values. Non highlighted rows are unique values
Simple
The link is broken, Jon. Thank you for this tutorial!!!
Thanks Ivan! I believe it is fixed now. 🙌
Hi, what if the bank statement has several values which sums up and only then ties back to the value in account books?
Great question Siddhant! There are a few ways to go about it.
One relatively simple way is to first create a summary report of the bank statement that has one row per transaction with the sum of all values that would match the account. This can be done with a pivot table or Power Query. Then use that report as the source of the bank statement in the table where you combine both sources.
There are other ways, but this is probably the easiest.
I hope that helps. Thanks again and have a nice day! 🙂
Do a step by step video on how to move check registers from QB to Excel. It's easy to transfer QB reports but registers are not so obvious
Thanks for the suggestion, Richard! I actually just recorded a video on cleaning up the bank register from QB and we're planning to post it next week. It covers both manual and automated solutions. Stay tuned. 🙂
@ExcelCampus Great! I have several years of two check registers that I need to export to Excel! BTW my QB is old. 2012 Deskto. I have Excel 365.
@@ExcelCampus Thanks!
The link to download the Excel file is giving an error
Sorry about that. It should be working now.
@@ExcelCampus It keeps giving an error. "404 error: Page not found"