Welcome to my channel! I'm Randy Hohn, a Phoenix, Arizona based Certified Public Accountant with over 30 years of experience in accounting, finance, systems and analytics.
This channel will help accountants and analysts get their work done faster and more accurately, using tools like: Excel, Power Query, Power Map, Power BI, DAX and Visual Basic.
If you want to be notified of new posts, please subscribe. Thanks for watching!
Yes, the payment amount can change from the model if the first payment date is not one month from the loan date. Interest accrues with the passing of time. Try this: Key over cell B11 with the new first payment date, and C11 with the new payment amount. Scroll down to the end and see if the loan "zeros out". Occasionally confirm the balance by checking the lender's web site or statements.
Nice work. Thanks for sharing this. I will definitely see if this can work for us. I am not sure if rows can always be in exactly the same order for most of the comparisons I see, but maybe there is some way to impose some kind of sort and then do a similar analysis.
Hello. If you filter and keep the nulls, you would be excluding the records that were not null, but had different amounts in both columns. However, you could use that method to count records that were in one table and not another.
Ok, few things. 1) if you have data is columns with same format and you want to make changes then select those columns before using "Replace values" 2)You don't have to drag a column to the beginning, simply right hand click go to move and select to beginning 3)When tables are identical columns don't add them manually. Instead of creating individual queries and then appending, open a blank query from other sources. Type =Excel.CurrentWorkbook() it should two columns Content and Name. Use the Filter to select the tables you want or to exclude those you don't. Click the expand icon on content, a dialogue box with available columns will appear. Assuming that your data in each is in same columns, just unclick use original then click ok. It will add a column called Name but you can rename this.
Great video without using merge and joints. Would you make a video to reconcile 2 tables, both with 3 rows - date, particulars and amounts. The recon should be done first with particulars matching, and then if particulars dont match, with dates matching
Yes, I will be posting one on how to reconcile amounts with multiple fields involved. It will be the same basic process, however you create a unique key by concatenating the fields together before the pivot. i.e. Date&"-"&Number&"-"&Department, etc. Then parsing this back out once the zeros are filtered out, to see differences by field.
Hi iankr. I don't think you have to, but working within the data model allows us to build the solution within that virtual space. Then, return just what we need to complete the report within Excel.
That's a great way to do the same thing! I recorded the "pivot table method" since I thought it would be easier to follow and learn for those already familiar with Excel Tables, and or Pivot Tables.
In Excel, dates are just numbers. Whole numbers. In the video, because they are part of the formula, they are part of the comparison. Date / Time combinations would be more challenging, as they are decimals. In this case, try an Integer function to group them by day. Or, group them by day within the pivot table.
Hi. I am using the concatenation operator to combine the contents of columns C, D and E. Enter this formula into B7 and copy all the down. =+C7&"-"&D7&"-"&E7
Hello! Here is a link to the file. You'll have to download it and open it in Excel to run the code. Good Luck! Randy docs.google.com/spreadsheets/d/1vQcVuNZIUMf6D3t8IgtRR7z88e3He1Tb/edit?usp=sharing&ouid=112934880298982104184&rtpof=true&sd=true
Hi Suban, Here is a link to the file. You should be able to download and save it. docs.google.com/spreadsheets/d/1OtAO28czd19pTwCEaGV8PaoaU3LG4Cqk/edit?usp=sharing&ouid=112934880298982104184&rtpof=true&sd=true
Hi. Here is a link to the folder with the file. This reconciliation has been revised and I included the new one in the folder. The older one is 4+ years old, so I wrote a new one. Hopefully, this helps. Please let me know if you have trouble accessing. drive.google.com/drive/folders/11CPC_wTaCuS5ryRKuQ2jdYiMhfhX7Gnt?usp=sharing
Hi Vlad. I'm glad you liked the video! I'll check the link to make sure it works. I have been making changes to the website and most likely broke it. Randy
