Patricia asks how to mark the last record in each category. Episode #1274 shows you how to use 'old-school' Conditional Formatting formulas to solve the problem. So, what do a Database, an Absolute Column Reference and a Relative Cell Reference have in common? Find out in Episode #1274!
...This blog is the video 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!
For all of your Microsoft Excel needs visit MrExcel.com -- Your One Stop for Excel Tips and Solutions.
...This blog is the video 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!
For all of your Microsoft Excel needs visit MrExcel.com -- Your One Stop for Excel Tips and Solutions.
Transcript of the video:
Just go and checkout videos on Easy-XL.com, it does amazing things in excel.
Learn Excel from MrExcel podcast episode 1275, Mark The Last.
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
Today's question, sent in from Arizona, from Patricia. Patricia has a large database thousands of rows.
She said, and she needs to go through and mark the last record every time that this column changes and I've already done it here, but I want to show you how to do this is...
We're gonna go back and gotta use some old old conditional formatting what we want to do is, start in the first row, row 2 don't include the headings.
Select the whole database. Now you have to be aware of the fact that the active cell in this case is in row 2, that's going to be really important.
We're gonna to write a formula here, it's going to refer to the active cell.
Alright so, Home Tab, Styles, Conditional Formatting, all the way down at the bottom New Rules, it's none of these that are built in New Rule.
And we're going to say we're going to use a formula, to determine which cells to format, formula and I am gonna write a formula, that is going to be based on cell A2.
So, pretend we're in A2 and we're looking out to see if this is the last item or not.
So we say =$E2<>$E3.
All right, and that's really important, what we're doing? We're saying, hey.
go look in this row, column E of this row and see if it's not equal to column E of the next row.
Notice, there is no dollar signs before the 2 or before the 3 but there are dollar signs before the E because this same formulas going to be applied to cell B2, C2 and D2 and all the way down.
All right, so we want the 2 and the 3 to be relative.
We want the E to be absolute. Okay, now that you have that formula.
Click format, will go through let's see, I'll choose a fill, let's go to the yellow fill font color red and bold.
I will click OK and then click OK and bam there, we have it every time that the Grade Level in this case changes.
We get that mark. Now, the assumption here is that your data is Sorted by Grade Level.
If the data is not sorted by Grade Level, then you have to go to a much more intense formula to actually kind of do a count if to see if this, occurrence of 13 matches, the number of Total Occurrences 13 in the whole row.
But, some of your data sorted. This is it.
You are good to go.
Thanks Patricia for sending a question in, and thanks to you see you next time with another nestcast.
Learn Excel from MrExcel podcast episode 1275, Mark The Last.
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
Today's question, sent in from Arizona, from Patricia. Patricia has a large database thousands of rows.
She said, and she needs to go through and mark the last record every time that this column changes and I've already done it here, but I want to show you how to do this is...
We're gonna go back and gotta use some old old conditional formatting what we want to do is, start in the first row, row 2 don't include the headings.
Select the whole database. Now you have to be aware of the fact that the active cell in this case is in row 2, that's going to be really important.
We're gonna to write a formula here, it's going to refer to the active cell.
Alright so, Home Tab, Styles, Conditional Formatting, all the way down at the bottom New Rules, it's none of these that are built in New Rule.
And we're going to say we're going to use a formula, to determine which cells to format, formula and I am gonna write a formula, that is going to be based on cell A2.
So, pretend we're in A2 and we're looking out to see if this is the last item or not.
So we say =$E2<>$E3.
All right, and that's really important, what we're doing? We're saying, hey.
go look in this row, column E of this row and see if it's not equal to column E of the next row.
Notice, there is no dollar signs before the 2 or before the 3 but there are dollar signs before the E because this same formulas going to be applied to cell B2, C2 and D2 and all the way down.
All right, so we want the 2 and the 3 to be relative.
We want the E to be absolute. Okay, now that you have that formula.
Click format, will go through let's see, I'll choose a fill, let's go to the yellow fill font color red and bold.
I will click OK and then click OK and bam there, we have it every time that the Grade Level in this case changes.
We get that mark. Now, the assumption here is that your data is Sorted by Grade Level.
If the data is not sorted by Grade Level, then you have to go to a much more intense formula to actually kind of do a count if to see if this, occurrence of 13 matches, the number of Total Occurrences 13 in the whole row.
But, some of your data sorted. This is it.
You are good to go.
Thanks Patricia for sending a question in, and thanks to you see you next time with another nestcast.
