 Oh, and welcome to this session. This is Professor Farhad and this session we would look for duplicate payments using Excel, whether those duplicate payments are due to error or fraud, we're gonna be using Excel techniques. These topics could be covered in an introduction to data analytics and accounting or simply a data analytics course, auditing course obviously, internal auditing or managerial accounting for internal control purposes. As always, I would like to remind you to connect with me on LinkedIn if you haven't done so. YouTube is where you would need to subscribe. I have 1,700 plus accounting, auditing, finance, tax, and Excel tutorial. If you like my lessons, please like them, share them, subscribe to the channel. If they help you, it means they might help others, share the wealth, connect with me on Instagram and on my website, farhadlectures.com, you will find additional resources to supplement your accounting education and especially pass your CPA exam, CMA exam, enrolled agent exam. So if you're looking to improve your score past your professional certification, move on with your life, please check out my website. To illustrate the concept of duplicate payments, we're gonna be looking at this Excel sheet where we only have 100 record. In the real world, you could have 100, 100,000 or 1 million record, you would use the same strategy to find duplicate payments. So what we have is the data transaction took place, the name of the person that input the transaction or wrote the check, whoever is responsible for this transaction, the memo for what purpose, the check number and the payment amount. So how do we start this investigation? How do we start our internal control procedure in order to find out if we have any duplicate payments? So what we want to find out first is if we have any duplicate payment on a particular date for the same amount and for the same purpose. So how do we do so? What we're gonna do, we're gonna create a new column called information. And under this column, we want to pull the date of the transaction and the memo, which is what purpose. And we don't wanna do the check number because each check number is unique and the payment. So we're looking for transaction with the same date, the same memo and the same payment. Now you could have also include the name of the person that wrote the check, but you don't have to. First, you're looking for duplicate. You could also see if that same individual also input the transaction. You can if you want to, but I'm gonna ignore this because there's another way to find out if it's the same person or another person, I will show you this at the end. What we did is in this column, so I included three pieces of information. The date, let me just highlight them so this way you know what we're looking for. I add it to the memo, add it to the payment. Once again, I could have add the name, but I'm gonna choose not to. And the check number supposed to be unique for looking for duplicate check numbers. We could use a different strategy, but the point here to find out if we have more than one check sent out on the same date for the same purpose. Maybe somebody made an error or someone is stealing money from the company. Who knows? It means they approve the transaction and they cut two checks. Now, once I have this information column, I'm just gonna go ahead, grab it and populate the remaining of the transactions. Now what's gonna happen is this, I'm looking for duplicates. So in order to find out what is the duplicate and the payment, I'm gonna look for count. So how many times in this column, we have the same transaction or the same cell counted more than once. So in this column here, I'm gonna use my count if function. And I do have a tutorial about the count if function. The count if function is a very powerful, a very powerful function, which is again, I do have a tutorial about this. You can check it out on my website, or at lectures.com because I do have those recording listed there. So I'm gonna do a count function, what I want to find out. I wanna look into this column, so I'm gonna highlight this column, column F, and I want to find out if I have any duplicates and basically duplicates for any cell. I'm gonna click on okay. And I see this transaction have no duplicate. I'm gonna take the formula and scroll all the way down. Hey, now it's gonna tell me and I could have hundreds or thousands of duplicates. Now at this point, you have more than one way to find out if you have any duplicates. One way to do it, I'm gonna start with conditional formatting. I'm gonna highlight the G column, go to conditional formatting, click on home, go on conditional formatting, and click on a new rule. And I want, and I want any cell format, any cell value that's greater than, click on greater than in the number one. So any cell with a value that more than one, please highlight it for me, basically tell me what it is. So I'm gonna just put here, I like the red, click on the red and click on okay. Now, what it did, it highlighted the cells. Now I might have a lot of them, I can scroll to see it. This is one way to do it. Another way to do this is to filter this column. So I'm gonna go ahead and filter this column, and I want to choose only the transaction with more than one. So I'm gonna click only keep the two, more than one, two or above if I have more than two, that's fine. Click on okay. And notice here I have, it seems I have two transactions that are duplicate. And notice one of them is recorded and on January 2nd by Bell, it's a purchase. And Bill sent two checks for the same amount, for the same purpose, for a purchase. So notice, I notice here that also now I know the name of the person, the date and everything and that person is billed. Now what can I do at this point? I can maybe take a picture of this for my record as if I'm an investigator because that's very important later on if this is gonna go down for fraud case or maybe this person is gonna be reprimanded. You wanna make sure you have the evidence or if it's just a mistake, you can delete the column but I'm gonna show you how to delete the duplicate column in a moment. But at least I know that Bill made this mistake. I also know that Virat and George also had a similar mistake by the end of the month for the salary. They paid $651 to twice. So one of them made a mistake because it cannot be the same amount on the same date or it could be, I don't know but this is what I need to find out. This is part of the internal control procedures. Now let me clear the filter, just kind of go back and select everything. What I can do too to remove the duplicate and you have to be very careful here. I don't like to delete anything on Excel. I'll just keep it, but to remove the duplicate, you could click on data and there is this function here on your computer might look a little bit different. It says remove duplicate. So you click on it and it's gonna select everything. And here you have to make, first of all, make sure you have my data has headers because my data has headers. You click on my data has headers and which duplicates do you want to do? The date is relevant. The name of the person is not relevant because they don't all have the same name. The memo is relevant. The check number is not relevant. So you wanna make sure you uncheck them. The information and the column also not relevant. Click on okay. And now it removed two duplicates and I'm left with 97 unique values. That's fine or the duplicates now are gone. But once again, you have to be very careful. Now also what you could have done because what I did here, I assumed the same payment took place on the same date. That might not be the case. In other words, when I started this investigation and I started this investigation into this file, I assumed that the duplicate transaction took place on the same date. It doesn't have to. Simply put, what I can do, I'm gonna delete everything and show you how else you can approach this if you don't think the transaction took on the same. Took place on the same date. So under information, you would click only on the amount and you would select also the memo. And here what you're looking for is duplicate for amount and memo. You don't care whether they took place on the same date or a different date because the idea is it could be that the person that's issuing those duplicate payment, they're not issue them on the same date. They're not writing the check on the same date. So that's another way to kind of pull the duplicate payments than do further investigation. But this is the whole purpose of this lesson is to show you how to look for this type of information. Now, once you find this information, maybe Bill needs required training, maybe Virat and George made an innocent mistake. I don't know, but this is how you zoom in on the problem and you take it from there. As always, if you like this recording, please click on the like button and I strongly suggest you visit my website for additional lectures and resources, farhatlectures.com, especially if you're studying for your CPA exam. Share the wealth, subscribe to the YouTube channel. Good luck and stay safe during those coronavirus days.