Jennifer from Daytona wants to Filter a data set to only show Tomato and then number the visible rows. But you can't do this with the Fill Handle. In today's episode, a couple of formulas to solve the problem.
Transcript of the video:
Learn Excel from MrExcel Podcast Episode 2328.
Number the visible rows.
Hey, welcome back to the MrExcel Netcast. I'm Bill Jelen.
Today's question from Jennifer in Daytona.
Jennifer has this data set.
She wants to filter to "Tomato" -- (tomatoes are her best selling product), and then number just the visible records and fill the other records with dash or --.
Alright, so here Filter by Selection.
I get all of the tomatoes and I'm like well this is going to be *easy*! Type the one. Hold down the Ctrl key.
Grab the Fill Handle and drag.
Doesn't work! Doesn't work, I guess, because we have a filter.
This is not going to work. So I said, "Jennifer...
"What do you do with all the other records?" She said, "I put a dash".
"Are you sure you're never going to enter numbers there?" She says, "Right!" So, here's what we're going to do.
We're going to count all of the numbers above us, so from E dollar sign 1 colon. Right now, we're in row four. So let's say E3.
Count how many numbers are above us and add one like that.
And then I copy that.
All the way down to the end of the data. Control V.
I get the numbers one through 134 and then when I clear the filter we should see that only the tomato records are numbered.
Now to put the dashes in, let's see how we're going to do. I'm going to choose all the other records.
And we use control G for Go To.
Click Special. Blanks. OK. And type apostrophe, dash, dash.
That way it's not a minus sign is not seen as a formula. Press Ctrl+Enter like that.
Alright, so provided that no one ever types a number in here that appears like it's going to work.
But then I started to think: Well if this was my data I might want to really number everything.
So we kill the filter. We don't need the filter here.
What I'm going to do is I'm going to count: =COUNTIF( Count of how many things above me and this is gonna be one of those awesome expanding ranges.
C$1 one to the current row C2. No dollar sign there on C2. That is equal to C2.
Double-Click to copy that down and we'll just get a mix of numbers 1,1, 2, 3, 3 and so on.
But the beautiful thing: Here, let's say I choose anything Kiwi and Filter by Selection.
I get the numbers 1,2,3,4,5,6 in order like that, right?
So while that's not what Jennifer was trying to do, but still a cool trick either way.
If you like these tips, please down below in YouTube, Subscribe and ring that bell.
Feel free to post any questions or comments down in the comments below. And check out my new book MrExcel 2020.
Seeing Excel Clearly.
Click that "I" in the top right-hand corner For more information.
Well, I want to thank you for stopping by.
I will see you next time for another netcast from MrExcel.
Number the visible rows.
Hey, welcome back to the MrExcel Netcast. I'm Bill Jelen.
Today's question from Jennifer in Daytona.
Jennifer has this data set.
She wants to filter to "Tomato" -- (tomatoes are her best selling product), and then number just the visible records and fill the other records with dash or --.
Alright, so here Filter by Selection.
I get all of the tomatoes and I'm like well this is going to be *easy*! Type the one. Hold down the Ctrl key.
Grab the Fill Handle and drag.
Doesn't work! Doesn't work, I guess, because we have a filter.
This is not going to work. So I said, "Jennifer...
"What do you do with all the other records?" She said, "I put a dash".
"Are you sure you're never going to enter numbers there?" She says, "Right!" So, here's what we're going to do.
We're going to count all of the numbers above us, so from E dollar sign 1 colon. Right now, we're in row four. So let's say E3.
Count how many numbers are above us and add one like that.
And then I copy that.
All the way down to the end of the data. Control V.
I get the numbers one through 134 and then when I clear the filter we should see that only the tomato records are numbered.
Now to put the dashes in, let's see how we're going to do. I'm going to choose all the other records.
And we use control G for Go To.
Click Special. Blanks. OK. And type apostrophe, dash, dash.
That way it's not a minus sign is not seen as a formula. Press Ctrl+Enter like that.
Alright, so provided that no one ever types a number in here that appears like it's going to work.
But then I started to think: Well if this was my data I might want to really number everything.
So we kill the filter. We don't need the filter here.
What I'm going to do is I'm going to count: =COUNTIF( Count of how many things above me and this is gonna be one of those awesome expanding ranges.
C$1 one to the current row C2. No dollar sign there on C2. That is equal to C2.
Double-Click to copy that down and we'll just get a mix of numbers 1,1, 2, 3, 3 and so on.
But the beautiful thing: Here, let's say I choose anything Kiwi and Filter by Selection.
I get the numbers 1,2,3,4,5,6 in order like that, right?
So while that's not what Jennifer was trying to do, but still a cool trick either way.
If you like these tips, please down below in YouTube, Subscribe and ring that bell.
Feel free to post any questions or comments down in the comments below. And check out my new book MrExcel 2020.
Seeing Excel Clearly.
Click that "I" in the top right-hand corner For more information.
Well, I want to thank you for stopping by.
I will see you next time for another netcast from MrExcel.
