Excel - How To Finds A Debit's Credit In Excel - Duel 156 - Episode 1872
18.6 هزار بار بازدید -
11 سال پیش
-
Microsoft Excel Tutorial - Finding
Microsoft Excel Tutorial - Finding Unmatched debits and credits in Excel.
You have a list of debuts and credits. For each amount, match it with the next matching negative amount. After doing all of the matches, find the items without a match. Bill uses VBA and Mike uses some clever math.
Welcome back to another episode of Dueling Excel, where Mike Girvin from Excel Is Fun and I, Bill Jelen from MrExcel, go head to head to find the best solution to your Excel problems. In this episode, we tackle the issue of unmatched debits and credits, a common problem faced by many Excel users.
The question comes from Colin on YouTube, who wants to match negative numbers with positive numbers in over 1000 lines of data. This can be a daunting task, especially when there are duplicate amounts to consider. The goal is to find the matching pairs and mark them off, leaving behind any unmatched debits or credits.
At first, I thought about using the Ctrl+F function to find the matching pairs. However, the issue with this approach is that it will only find the first instance of a negative number, not the second or third. So, I turned to VBA and used the AutoFilter function to solve this problem. The code is available for you to see and use in your own projects.
The AutoFilter function allows us to set up two ranges - one for the data and one for the visible cells. This is important because we need to start looking for visible cells from row 2, and sometimes there may not be any visible cells at all. So, we need to include an extra row in our range to avoid any errors.
To make the process more efficient, I have also included a loop that will go through the data 1000 times. This may seem like a lot, but it ensures that all the data is processed correctly. I have also turned off screen updating to avoid any flashing on the screen, but you can turn it back on while testing the code.
One important thing to note is that the code will not match any items that have already been matched. This is to ensure that the answers are correct and there are no duplicates. The code checks for this by looking at column J, which indicates whether an item has been matched or not.
After running the code, we can see that all the matching pairs have been marked with a number, while the unmatched items remain blank. We can then sort the data to bring the matching pairs together and easily identify any unmatched items.
Mike also has a solution to this problem using formulas, which he demonstrates in the video. He uses a running count and the COUNTIF function to identify the unmatched items. His method is much simpler and more efficient, making it a great alternative to using VBA.
In conclusion, there are multiple ways to solve the issue of unmatched debits and credits in Excel. Whether you prefer using VBA or formulas, both Mike and I have provided solutions that will help you tackle this problem with ease. We hope you found this episode helpful and we'll see you next week for another Dueling Excel podcast. Don't forget to subscribe to our channels for more Excel tips and tricks. Thanks for watching!
Buy Bill Jelen's latest Excel book: https://www.mrexcel.com/products/latest/
You can help my channel by clicking Like or commenting below: https://www.mrexcel.com/like-mrexcel-...
Table of Contents:
(00:00) Introduction to episode 156: Unmatched debits and credits
(00:22) Question from Colin at YouTube
(01:05) Using Ctrl + F to match negative and positive numbers
(03:31) Explanation of VBA code solution
(07:01) Formula solution using Running Count and COUNTIF
(11:06) Clicking Like really helps the algorithm
#excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftmvp #walkthrough #evergreen #spreadsheetskills #analytics #analysis #dataanalysis #dataanalytics #mrexcel #spreadsheets #spreadsheet #excelhelp #accounting #tutorial #excelduel #excelvba #excelformula
This video answers these common search terms:
how to finds a debit's credit in excel
matching debits and credits with opposite sign
matching negative to positive amounts
how to match debits and credits in excel
how to match off credits and debit in excel
how to do a debit and credit spread sheet in excel
Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads...
You have a list of debuts and credits. For each amount, match it with the next matching negative amount. After doing all of the matches, find the items without a match. Bill uses VBA and Mike uses some clever math.
Welcome back to another episode of Dueling Excel, where Mike Girvin from Excel Is Fun and I, Bill Jelen from MrExcel, go head to head to find the best solution to your Excel problems. In this episode, we tackle the issue of unmatched debits and credits, a common problem faced by many Excel users.
The question comes from Colin on YouTube, who wants to match negative numbers with positive numbers in over 1000 lines of data. This can be a daunting task, especially when there are duplicate amounts to consider. The goal is to find the matching pairs and mark them off, leaving behind any unmatched debits or credits.
At first, I thought about using the Ctrl+F function to find the matching pairs. However, the issue with this approach is that it will only find the first instance of a negative number, not the second or third. So, I turned to VBA and used the AutoFilter function to solve this problem. The code is available for you to see and use in your own projects.
The AutoFilter function allows us to set up two ranges - one for the data and one for the visible cells. This is important because we need to start looking for visible cells from row 2, and sometimes there may not be any visible cells at all. So, we need to include an extra row in our range to avoid any errors.
To make the process more efficient, I have also included a loop that will go through the data 1000 times. This may seem like a lot, but it ensures that all the data is processed correctly. I have also turned off screen updating to avoid any flashing on the screen, but you can turn it back on while testing the code.
One important thing to note is that the code will not match any items that have already been matched. This is to ensure that the answers are correct and there are no duplicates. The code checks for this by looking at column J, which indicates whether an item has been matched or not.
After running the code, we can see that all the matching pairs have been marked with a number, while the unmatched items remain blank. We can then sort the data to bring the matching pairs together and easily identify any unmatched items.
Mike also has a solution to this problem using formulas, which he demonstrates in the video. He uses a running count and the COUNTIF function to identify the unmatched items. His method is much simpler and more efficient, making it a great alternative to using VBA.
In conclusion, there are multiple ways to solve the issue of unmatched debits and credits in Excel. Whether you prefer using VBA or formulas, both Mike and I have provided solutions that will help you tackle this problem with ease. We hope you found this episode helpful and we'll see you next week for another Dueling Excel podcast. Don't forget to subscribe to our channels for more Excel tips and tricks. Thanks for watching!
Buy Bill Jelen's latest Excel book: https://www.mrexcel.com/products/latest/
You can help my channel by clicking Like or commenting below: https://www.mrexcel.com/like-mrexcel-...
Table of Contents:
(00:00) Introduction to episode 156: Unmatched debits and credits
(00:22) Question from Colin at YouTube
(01:05) Using Ctrl + F to match negative and positive numbers
(03:31) Explanation of VBA code solution
(07:01) Formula solution using Running Count and COUNTIF
(11:06) Clicking Like really helps the algorithm
#excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftmvp #walkthrough #evergreen #spreadsheetskills #analytics #analysis #dataanalysis #dataanalytics #mrexcel #spreadsheets #spreadsheet #excelhelp #accounting #tutorial #excelduel #excelvba #excelformula
This video answers these common search terms:
how to finds a debit's credit in excel
matching debits and credits with opposite sign
matching negative to positive amounts
how to match debits and credits in excel
how to match off credits and debit in excel
how to do a debit and credit spread sheet in excel
Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads...
11 سال پیش
در تاریخ 1393/01/01 منتشر شده
است.
18,626
بـار بازدید شده