Shawn sends in today's question. The spreadsheet has billing data to audit. For each record, there is a location, a start date, and an end date. Shawn wants to find any records where the same date and location is double-billed. A quick two-loop macro solves this problem. Episode 1017 shows you how.
This video is the podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
This video is the podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
Transcript of the video:
Hey! Welcome back to the MrExcel netcast.
I'm Bill Jelen.
Basically, you start out with massive amount of data.
How we gonna analyze as well let's fire up a pivot table.
See if we can solve this problem.
Hey! Welcome back to the MrExcel netcast.
I'm Bill Jelen.
Today's question is send in by Sean.
Sean watches the podcast and says wanted to figure out if there's a way that he could audit this spreadsheet.
This is a list of locations, a start date and an end date.
That they build for that location and they want to see if there's any locations that were double billed.
So, you know one ID was there and another ID was there.
He says that's an impossibility.
And I started to think about this and this seems similar to a situation that I have in the book, Excel Gurus Gone Wild.
You know but rather than dig out that book and figure out how the formula works.
I said I could probably just knock out some VBA very quickly, to do this.
So, I see there we go from row 2 to row 31.
Let's switch over to VBA, sub check them.
Say for "i = 2 to 31" those are the rows and then I'm going to have a second loop inside of that.
For J = cells(i, Let's see where's that first date.
That first date is in three, column C. Dot value to cells(i,4) dot value.
Now cells, is a better way in VBA to refer to a specific cell rather than using range where we have to build the formula.
We just specify the row number and the column number and it's nice here.
The row number can be a variable and what I'm going to do, see I need another variable here.
I didn't think about variable called Next Row.
Next Row, is equal to 2.
Alright! So, here's what we're going to do when I say cells next row.
So, in that case we're going to take this from row 2, Comma let's see which column are we and where in column G.
Which is column 7.
is equal to I want to copy the patient ID.
So, cells 'i', 2.
Don't even need to put value there.
By the way, it's the default property.
Copy a few more down.
So, in row G, we're putting the ID and in row H, we're putting the location which comes from column 2 and column 9 the date column.
We're going to put the actual date.
So, this is going to be simple.
We're just going to use the variable J, and then in column 10.
I'm going to build a concatenated key.
I can catenate a key that's going to take a location and the date and put them together.
So, I'm going to now say dot formula R1 C1 is equal to equal RC minus 2.
Ampersand (&) equal RC minus 1.
Probably several different ways to write that formula.
I'm more comfortable with R1, C1 at this point.
If you're new to macros here.
I don't want to write that as a regular.
A bit of code, and then we need to increment that counter variable next row is equal to next row plus 1.
So, what's this going to do?
It's going to go from every row from 2 to 31, and then within each row we're gonna say hey start at the earliest date.
July 4th 2008 and go to the later date July 21st and for each one of those dates write a new record over here, to columns G through J.
So, let's just do a quick test here.
We'll run it. All the way, look for [ check them ], click [ run ], an extra equal sign there and here we are.
So, we have location 435b is there for every day from the 4th through the 21st and a location 234a here from row 2 is in there for a few days.
Alright! You'll see that we now have far more records over, in G through J.
Now, what can we do?
We can check for duplicate.
I'm going to sort by my concatenated key column and say equal IF actually I do equal or if this is equal to the next value or if it's equal to the previous value then we want to know about it.
So, we just copy that down and cruise through there looking for trues right there.
There's a whole bunch of situations where we have the same location but different patient IDs.
So, just a quick and dirty way to solve this without having to really think about any formulas, any hard formulas at all.
Just a few lines simple VBA code.
Hey! Yesterday, I was at the University of Akron, teaching my power macros for Excel class.
It's a holiday class ever want to come to Akron, Ohio.
Now, we teach that about three times a year.
But if you want to learn VBA how to be able to knock out little bits of code like this.
Fine folks! Few have taken my power Excel class from the University of Akron.
Put it on a nice DVD-ROM.
You can slip it in, watch five minutes a day and before you know it you'll be up to speed writing code like this.
So, check that out we have it at our website.
Amazon all kinds of books.
I think it's 49 bucks or something like that.
So, great deal lets you create nice simple solutions like this one.
I want to thank Sean for sending this question in.
I want to thank you for stopping by.
I'll see you next time for another netcast from MrExcel.
Thanks for stopping by.
We'll see you next time for another netcast from MrExcel.
I'm Bill Jelen.
Basically, you start out with massive amount of data.
How we gonna analyze as well let's fire up a pivot table.
See if we can solve this problem.
Hey! Welcome back to the MrExcel netcast.
I'm Bill Jelen.
Today's question is send in by Sean.
Sean watches the podcast and says wanted to figure out if there's a way that he could audit this spreadsheet.
This is a list of locations, a start date and an end date.
That they build for that location and they want to see if there's any locations that were double billed.
So, you know one ID was there and another ID was there.
He says that's an impossibility.
And I started to think about this and this seems similar to a situation that I have in the book, Excel Gurus Gone Wild.
You know but rather than dig out that book and figure out how the formula works.
I said I could probably just knock out some VBA very quickly, to do this.
So, I see there we go from row 2 to row 31.
Let's switch over to VBA, sub check them.
Say for "i = 2 to 31" those are the rows and then I'm going to have a second loop inside of that.
For J = cells(i, Let's see where's that first date.
That first date is in three, column C. Dot value to cells(i,4) dot value.
Now cells, is a better way in VBA to refer to a specific cell rather than using range where we have to build the formula.
We just specify the row number and the column number and it's nice here.
The row number can be a variable and what I'm going to do, see I need another variable here.
I didn't think about variable called Next Row.
Next Row, is equal to 2.
Alright! So, here's what we're going to do when I say cells next row.
So, in that case we're going to take this from row 2, Comma let's see which column are we and where in column G.
Which is column 7.
is equal to I want to copy the patient ID.
So, cells 'i', 2.
Don't even need to put value there.
By the way, it's the default property.
Copy a few more down.
So, in row G, we're putting the ID and in row H, we're putting the location which comes from column 2 and column 9 the date column.
We're going to put the actual date.
So, this is going to be simple.
We're just going to use the variable J, and then in column 10.
I'm going to build a concatenated key.
I can catenate a key that's going to take a location and the date and put them together.
So, I'm going to now say dot formula R1 C1 is equal to equal RC minus 2.
Ampersand (&) equal RC minus 1.
Probably several different ways to write that formula.
I'm more comfortable with R1, C1 at this point.
If you're new to macros here.
I don't want to write that as a regular.
A bit of code, and then we need to increment that counter variable next row is equal to next row plus 1.
So, what's this going to do?
It's going to go from every row from 2 to 31, and then within each row we're gonna say hey start at the earliest date.
July 4th 2008 and go to the later date July 21st and for each one of those dates write a new record over here, to columns G through J.
So, let's just do a quick test here.
We'll run it. All the way, look for [ check them ], click [ run ], an extra equal sign there and here we are.
So, we have location 435b is there for every day from the 4th through the 21st and a location 234a here from row 2 is in there for a few days.
Alright! You'll see that we now have far more records over, in G through J.
Now, what can we do?
We can check for duplicate.
I'm going to sort by my concatenated key column and say equal IF actually I do equal or if this is equal to the next value or if it's equal to the previous value then we want to know about it.
So, we just copy that down and cruise through there looking for trues right there.
There's a whole bunch of situations where we have the same location but different patient IDs.
So, just a quick and dirty way to solve this without having to really think about any formulas, any hard formulas at all.
Just a few lines simple VBA code.
Hey! Yesterday, I was at the University of Akron, teaching my power macros for Excel class.
It's a holiday class ever want to come to Akron, Ohio.
Now, we teach that about three times a year.
But if you want to learn VBA how to be able to knock out little bits of code like this.
Fine folks! Few have taken my power Excel class from the University of Akron.
Put it on a nice DVD-ROM.
You can slip it in, watch five minutes a day and before you know it you'll be up to speed writing code like this.
So, check that out we have it at our website.
Amazon all kinds of books.
I think it's 49 bucks or something like that.
So, great deal lets you create nice simple solutions like this one.
I want to thank Sean for sending this question in.
I want to thank you for stopping by.
I'll see you next time for another netcast from MrExcel.
Thanks for stopping by.
We'll see you next time for another netcast from MrExcel.
