If you work in Accounts Payable, check out the great resources at AP-Now.com. Today's question from a recent webinar that I did for the AP-Now members: Given an Excel worksheet of payments made by accounts payable, how can you look for duplicate payments.
My solution involves the new TEXTJOIN function, the Mark Duplicates feature in Conditional Formatting, and then Filter by Color.
My solution involves the new TEXTJOIN function, the Mark Duplicates feature in Conditional Formatting, and then Filter by Color.
Transcript of the video:
Learn Excel from MrExcel Podcast Episode 2283. Find Duplicate AP Payments Based on Two Columns.
Hey, welcome back to MrExcel Netcast. I'm Bill Jelen. I was doing a webinar recently for AP. Now the accounts payable folks and one of the questions that came up during the webinar was a great one. They said hey so I had a database of payments. You know we paid this amount to this vendor on this day and someone said wait, how can we use this to look for duplicate payments and what they wanted to do is they want to find any duplication between vendor and amount. Now, there's a chance that we pay a vendor the same amount every month and so that isn't necessarily a duplicate payment. But is there a way to try and find the possible duplicates right? And Yes, on the Home tab we have, you know, this Conditional Formatting, Highlight Cells, Duplicate Values, but that is only going to work with one column. So my suggestion was to come out here and add a new column called.
Look for Duplicates.
Right, and because I'm in Office 365, I could use this great new function: TEXTJOIN. The delimiter is going to be a vertical bar. Ignore_empty? Yes.
and then the two things we want to add: our vendor and amount like that. See what that does is that gives us both the name and the amount separated by the vertical bar in one column. Now if you're not in Office 365 then you could do this the old-fashioned way with =A2&"|"&E2.
Your call. Either way, I prefer to use TEXTJOIN, so I will go that way. Now that we have this look for duplicates over here, Control shift Down Arrow to select them all.
Ctrl backspace to bring it back. Conditional Formatting, Highlight Cells, Duplicate Values, and I'm just going to leave.
the default color there, and then if we cruise down through here, we will see some alright, but the fast way to do this is to turn on the Filter: Data. Filter, and then here we're going to Filter by Color to the pink cells. And now we have all of our potential duplicate payments that we can go check. Cool question that came in from the Accounts Payable folks and my solution there. Hey, if you like the tips in this video, please Subscribe and ring that bell. Feel free to post any questions or comments in the comments below. My new book, MrExcel LX. Click the "i" in the top right hand corner to get to that. I want to thank you for stopping by. Will see next time for another netcast for MrExcel.
Hey, welcome back to MrExcel Netcast. I'm Bill Jelen. I was doing a webinar recently for AP. Now the accounts payable folks and one of the questions that came up during the webinar was a great one. They said hey so I had a database of payments. You know we paid this amount to this vendor on this day and someone said wait, how can we use this to look for duplicate payments and what they wanted to do is they want to find any duplication between vendor and amount. Now, there's a chance that we pay a vendor the same amount every month and so that isn't necessarily a duplicate payment. But is there a way to try and find the possible duplicates right? And Yes, on the Home tab we have, you know, this Conditional Formatting, Highlight Cells, Duplicate Values, but that is only going to work with one column. So my suggestion was to come out here and add a new column called.
Look for Duplicates.
Right, and because I'm in Office 365, I could use this great new function: TEXTJOIN. The delimiter is going to be a vertical bar. Ignore_empty? Yes.
and then the two things we want to add: our vendor and amount like that. See what that does is that gives us both the name and the amount separated by the vertical bar in one column. Now if you're not in Office 365 then you could do this the old-fashioned way with =A2&"|"&E2.
Your call. Either way, I prefer to use TEXTJOIN, so I will go that way. Now that we have this look for duplicates over here, Control shift Down Arrow to select them all.
Ctrl backspace to bring it back. Conditional Formatting, Highlight Cells, Duplicate Values, and I'm just going to leave.
the default color there, and then if we cruise down through here, we will see some alright, but the fast way to do this is to turn on the Filter: Data. Filter, and then here we're going to Filter by Color to the pink cells. And now we have all of our potential duplicate payments that we can go check. Cool question that came in from the Accounts Payable folks and my solution there. Hey, if you like the tips in this video, please Subscribe and ring that bell. Feel free to post any questions or comments in the comments below. My new book, MrExcel LX. Click the "i" in the top right hand corner to get to that. I want to thank you for stopping by. Will see next time for another netcast for MrExcel.
