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.
Transcript of the video:
Hey, welcome back. It's time for another dueling excel podcast. I'm Bill Jelen from MrExcel.
I'll be joined by Mike Gervin from Excel Is Fun.
This is our episode 156.
Unmatched debits and credits.
Hey welcome back. It's time for another dueling excel podcast and boy I'm glad this is not episode 155 because that was a monster.
This one hopefully should be easier.
Question from Colin at YouTube.
Want to match negative numbers with positive numbers in over a 1000 lines.
So when we have a 100 find the -100 there are duplicate amounts.
So we have to be able to handle that what I think is happening here.
It sounds like we have a bunch of positive debits or credits and then the corresponding match.
Maybe like this was an invoice for 452 dollars and we want to go through and find Ctrl+F - 452 dollars So, right there those two items are matched.
We want to mark them off and after marking everything off. We're gonna be left with in a sense invoices and that haven't been paid or debits without matching credits and so as I thought about this, I thought about using Ctrl + F but the tough part is those duplicates. Alright when we go to find a negative 452.
Here, I'm going to find the first one, but then when I go and try and find a negative 452 here, I don't want to find the first negative 452 I want to find the second one and that seemed like that was going to be tough to do with Ctrl + F.
I'll press Alt+F11 here, and we'll take a look at this code.
My choice for the VBA was to use the AutoFilter or it's just called Filter. Now.
You'll see that we set up two ranges.
MyRange and DataRange and the MyRange is all of the data starting from cell H1 and that's what I'm going to apply the AutoFilter to.
But then after I apply the AutoFilter. I want to start looking for visible cells and that has to start in row 2 and so DataRange you see starts in row 2 and initially the size of this was final row - 1 because I wanted to go to just the last row but sometimes we're looking for an item that does not have a match and the visible cells only feature will fail. If there are no visible cells.
So I had to go to one extra row so both of these are the same size. This one starts at row 2 the first row of data and includes one blank row below the data.
This is a loop that's going to go through and do something a 1000 times.
And because of that I turned off screen updating here.
It's that way the screen, it does not flash.
Although as we're testing it, we need to leave that back on so we can see what's going on.
Application status bar just tells us the status and then the other thing I realized the first time I ran this actually recorded the video and was ready to send it off to Mike.
I Realized the answers were all wrong.
If something's already been matched so when we find that negative 452 and we have an answer out here in J indicating that's been matched. We don't want to go find the the other one we don't want to look any further so, we have to look here in column J, which is the 10th column and make sure that there's nothing been entered there, and then if that's TRUE, what are we gonna look for. LF is my variable for look for.
It's the negative of whatever value is here.
So let's let's just run this code as I talk.
So we go through alright, so.
Here we are. We're on row 2 and what are we looking for we're looking for a negative 452. So we start with the AutoFilters off.
We turn the AutoFilters on.
Go to the third column in our data set and look for things that are not.
Look for the blanks which in this case everything is currently a blank and then go to column 2 and look for negative 452. So I'll press F8 to run that and look this we get three of them okay, and we have no indication which one's right.
So I'm going to assume that it is the first one, that we are going to match. So I bring this back here, so we can see it.
Check the DataRange.
That's the one that starts in row 2 and extends down to row 731.
SpecialCells(xlCellTypeVisible) This is just like doing Go to Special Visible Cells Only See if we have more than one cell there, if that's TRUE then the MatchRow is the first cell. So let's hover over there. You'll see that they are matching it to row 39. All right, so.
I'm going to put the number 2 in both the original data and the second data.
We can't see the original data right now but I'll run that code.
See there's the number 2 End If and then turn off the AutoFilter after each one and so we end up with a 2 there and also, 2 down here in row 39 to indicate that those are matched.
And my idea is we just let this go through and process every single one so here we are looking for the 201's and there was only one match.
So those both get marked for the number 3 and then we're looking for the 99s and there were a bunch of matches.
And so the 2 get marked with a number 4 and now we're looking for a 496. Oh and there's no match here, so the IF statement will just skip over all of this and nothing gets marked in that particular case, and it just keeps going so I'll press the run button here.
You see it's actually kind of slow because I left the application screen updating ON All right there we go.
That was It was bad.
Having us in there once I actually pause the video so you wouldn't have to sit there and watch that let's take the application screen. I'm taking out this way you can actually see what the difference is.
So we'll clear all this data that we've matched up.
Press the Delete key and Alt F8.
Run.
See the screen doesn't change at all here but down the lower left-hand corner because there's application status part.
We can see in another just a few seconds how to do everything.
All right so now anything that has a blank over here is the items that are not matched.
We could actually sort the data A to Z by column J and that will bring the matching records together.
So there's the 452 and - 452 the 201 and the - 201 and then down at the end we have all of the records without a match.
So there you know that VBA code that's the solution like I can't imagine that you have a formula that would do this, but let's see what you have.
Thanks MrExcel.
VBA I do not know VBA, so I'm stuck doing formulas. Ah you know actually I got tripped up on this, but eventually found a solution.
I think that works, so.
Here's this smaller data set just so we can kind of make sense of it.
There's 200 and I need to find the first opposite.
There's another 200 find the first opposite but boom there's some unmatched 200s. If I come to this 500, I don't see anything down here, right.
600 there's a match here. Come down to the 458, there's a match here, but there's an unmatched minus here, so the white ones will be our unmatched ones. Now I'm going to start by doing a Running Count. So I'm going to say hey that range and that criteria.
Now how I'm going to construct this as I want an expandable range, so I'll lock the first one F4.
This will expand right now. I'll get a count of 1 but when I get down to the second 200 I'll get a 2. Down here, I'll get a 3 and a 4.
Ctrl + Enter Double click and send it down. If I put this in edit mode you can see how it's expanded as I copy it down with the criteria always relative cell reference 3 & 4.
Now I'm going to count the opposite.
So I'll use COUNTIF without an expandable range.
So I'll highlight that whole range and instead of simply clicking there, I'm going to minus so I'm counting for every 200 I see in the list.
I'm going to actually see the total count of the opposites.
Ctrl + Enter Double click and send it down. So there's a 2 a 2 and even down here for these unmatched numbers, it still says 2 but notice what the Running Count says, how many 200s are there?
3 How many opposites of 200s are there?
There's 2. So I simply say anytime the Running Count is greater than the count of the opposites, double click and send it down and there we get our patterns of TRUEs. Right TRUEs.
TRUEs and TRUEs Those are the unmatched so I could come over here =COUNTIF I do my expandable range and my criteria locking Boop with the F4 key anytime you are greater than COUNTIF of the whole range.
Whoops I mean notice my cursor moving, and I want to use my arrows to get the cell references.
So I have to hit F2 to go from I should have shown you that down here so down here, it says edit. So edit means that my arrows move the flashing cursor but now look down here edit.
If I hit F2 its back to enter so now my cursor will work to get the cell reference.
Ctrl+ Shift+ down arrow+ F4 comma and I need the opposite of that. Close parentheses.
Control + Enter Double click and send it down.
Then I'm gonna select a single cell. Ctrl + Shift + L.
Then I'll go ahead and uncheck FALSE, it'll give me TRUE.
Control + Shift + down arrow, and watch this Ctrl + C I love filter look at that the dancing ants are highlighting just the visible cells.
Now I'm gonna come over here whoops. I wasn't supposed to have that there.
I don't know if the dancing ants are still going Ctrl+C.
Come over here Ctrl+V and then these, this is the list MrExcel got.
Here's the one I got. I'm just going to check, and I'm going to join that with the & the serial number to the data and then say are you equal to the serial number and the Sorry the serial number date and the number serial number date and the number.
Ctrl+Enter Double click and send it down. Looks like I got a bunch of TRUEs.
Alright Ctrl + Up arrow Ctrl + Shift + L to turn that off. All right, will throw it back to MrExcel.
Point to Excel Is Fun Math wins that is so much easier than my method. Great way to go Mike.
Alright I want to thank everyone for stopping by.
Will see you next week for another dueling excel podcast from MrExcel. Excel Is Fun.
I'll be joined by Mike Gervin from Excel Is Fun.
This is our episode 156.
Unmatched debits and credits.
Hey welcome back. It's time for another dueling excel podcast and boy I'm glad this is not episode 155 because that was a monster.
This one hopefully should be easier.
Question from Colin at YouTube.
Want to match negative numbers with positive numbers in over a 1000 lines.
So when we have a 100 find the -100 there are duplicate amounts.
So we have to be able to handle that what I think is happening here.
It sounds like we have a bunch of positive debits or credits and then the corresponding match.
Maybe like this was an invoice for 452 dollars and we want to go through and find Ctrl+F - 452 dollars So, right there those two items are matched.
We want to mark them off and after marking everything off. We're gonna be left with in a sense invoices and that haven't been paid or debits without matching credits and so as I thought about this, I thought about using Ctrl + F but the tough part is those duplicates. Alright when we go to find a negative 452.
Here, I'm going to find the first one, but then when I go and try and find a negative 452 here, I don't want to find the first negative 452 I want to find the second one and that seemed like that was going to be tough to do with Ctrl + F.
I'll press Alt+F11 here, and we'll take a look at this code.
My choice for the VBA was to use the AutoFilter or it's just called Filter. Now.
You'll see that we set up two ranges.
MyRange and DataRange and the MyRange is all of the data starting from cell H1 and that's what I'm going to apply the AutoFilter to.
But then after I apply the AutoFilter. I want to start looking for visible cells and that has to start in row 2 and so DataRange you see starts in row 2 and initially the size of this was final row - 1 because I wanted to go to just the last row but sometimes we're looking for an item that does not have a match and the visible cells only feature will fail. If there are no visible cells.
So I had to go to one extra row so both of these are the same size. This one starts at row 2 the first row of data and includes one blank row below the data.
This is a loop that's going to go through and do something a 1000 times.
And because of that I turned off screen updating here.
It's that way the screen, it does not flash.
Although as we're testing it, we need to leave that back on so we can see what's going on.
Application status bar just tells us the status and then the other thing I realized the first time I ran this actually recorded the video and was ready to send it off to Mike.
I Realized the answers were all wrong.
If something's already been matched so when we find that negative 452 and we have an answer out here in J indicating that's been matched. We don't want to go find the the other one we don't want to look any further so, we have to look here in column J, which is the 10th column and make sure that there's nothing been entered there, and then if that's TRUE, what are we gonna look for. LF is my variable for look for.
It's the negative of whatever value is here.
So let's let's just run this code as I talk.
So we go through alright, so.
Here we are. We're on row 2 and what are we looking for we're looking for a negative 452. So we start with the AutoFilters off.
We turn the AutoFilters on.
Go to the third column in our data set and look for things that are not.
Look for the blanks which in this case everything is currently a blank and then go to column 2 and look for negative 452. So I'll press F8 to run that and look this we get three of them okay, and we have no indication which one's right.
So I'm going to assume that it is the first one, that we are going to match. So I bring this back here, so we can see it.
Check the DataRange.
That's the one that starts in row 2 and extends down to row 731.
SpecialCells(xlCellTypeVisible) This is just like doing Go to Special Visible Cells Only See if we have more than one cell there, if that's TRUE then the MatchRow is the first cell. So let's hover over there. You'll see that they are matching it to row 39. All right, so.
I'm going to put the number 2 in both the original data and the second data.
We can't see the original data right now but I'll run that code.
See there's the number 2 End If and then turn off the AutoFilter after each one and so we end up with a 2 there and also, 2 down here in row 39 to indicate that those are matched.
And my idea is we just let this go through and process every single one so here we are looking for the 201's and there was only one match.
So those both get marked for the number 3 and then we're looking for the 99s and there were a bunch of matches.
And so the 2 get marked with a number 4 and now we're looking for a 496. Oh and there's no match here, so the IF statement will just skip over all of this and nothing gets marked in that particular case, and it just keeps going so I'll press the run button here.
You see it's actually kind of slow because I left the application screen updating ON All right there we go.
That was It was bad.
Having us in there once I actually pause the video so you wouldn't have to sit there and watch that let's take the application screen. I'm taking out this way you can actually see what the difference is.
So we'll clear all this data that we've matched up.
Press the Delete key and Alt F8.
Run.
See the screen doesn't change at all here but down the lower left-hand corner because there's application status part.
We can see in another just a few seconds how to do everything.
All right so now anything that has a blank over here is the items that are not matched.
We could actually sort the data A to Z by column J and that will bring the matching records together.
So there's the 452 and - 452 the 201 and the - 201 and then down at the end we have all of the records without a match.
So there you know that VBA code that's the solution like I can't imagine that you have a formula that would do this, but let's see what you have.
Thanks MrExcel.
VBA I do not know VBA, so I'm stuck doing formulas. Ah you know actually I got tripped up on this, but eventually found a solution.
I think that works, so.
Here's this smaller data set just so we can kind of make sense of it.
There's 200 and I need to find the first opposite.
There's another 200 find the first opposite but boom there's some unmatched 200s. If I come to this 500, I don't see anything down here, right.
600 there's a match here. Come down to the 458, there's a match here, but there's an unmatched minus here, so the white ones will be our unmatched ones. Now I'm going to start by doing a Running Count. So I'm going to say hey that range and that criteria.
Now how I'm going to construct this as I want an expandable range, so I'll lock the first one F4.
This will expand right now. I'll get a count of 1 but when I get down to the second 200 I'll get a 2. Down here, I'll get a 3 and a 4.
Ctrl + Enter Double click and send it down. If I put this in edit mode you can see how it's expanded as I copy it down with the criteria always relative cell reference 3 & 4.
Now I'm going to count the opposite.
So I'll use COUNTIF without an expandable range.
So I'll highlight that whole range and instead of simply clicking there, I'm going to minus so I'm counting for every 200 I see in the list.
I'm going to actually see the total count of the opposites.
Ctrl + Enter Double click and send it down. So there's a 2 a 2 and even down here for these unmatched numbers, it still says 2 but notice what the Running Count says, how many 200s are there?
3 How many opposites of 200s are there?
There's 2. So I simply say anytime the Running Count is greater than the count of the opposites, double click and send it down and there we get our patterns of TRUEs. Right TRUEs.
TRUEs and TRUEs Those are the unmatched so I could come over here =COUNTIF I do my expandable range and my criteria locking Boop with the F4 key anytime you are greater than COUNTIF of the whole range.
Whoops I mean notice my cursor moving, and I want to use my arrows to get the cell references.
So I have to hit F2 to go from I should have shown you that down here so down here, it says edit. So edit means that my arrows move the flashing cursor but now look down here edit.
If I hit F2 its back to enter so now my cursor will work to get the cell reference.
Ctrl+ Shift+ down arrow+ F4 comma and I need the opposite of that. Close parentheses.
Control + Enter Double click and send it down.
Then I'm gonna select a single cell. Ctrl + Shift + L.
Then I'll go ahead and uncheck FALSE, it'll give me TRUE.
Control + Shift + down arrow, and watch this Ctrl + C I love filter look at that the dancing ants are highlighting just the visible cells.
Now I'm gonna come over here whoops. I wasn't supposed to have that there.
I don't know if the dancing ants are still going Ctrl+C.
Come over here Ctrl+V and then these, this is the list MrExcel got.
Here's the one I got. I'm just going to check, and I'm going to join that with the & the serial number to the data and then say are you equal to the serial number and the Sorry the serial number date and the number serial number date and the number.
Ctrl+Enter Double click and send it down. Looks like I got a bunch of TRUEs.
Alright Ctrl + Up arrow Ctrl + Shift + L to turn that off. All right, will throw it back to MrExcel.
Point to Excel Is Fun Math wins that is so much easier than my method. Great way to go Mike.
Alright I want to thank everyone for stopping by.
Will see you next week for another dueling excel podcast from MrExcel. Excel Is Fun.
