00:00If you need to automatically generate sequential serial number or index number for your rows like
00:04this, you have two distinct methods at your disposal using countA function. All these methods
00:10work regardless of whether you delete, hide rows, and or apply filter. I'm going to introduce a
00:15filter by pressing Ctrl Shift L to start off with. And you're going to place your cursor at cell A2.
00:21For the first method, you're going to use subtotal. And the first argument on subtotal will be
00:26number three, which is function number three. Click here, double click, or you can put three here if
00:31you like, comma, B2. You're going to press F4 once to make B2 an absolute reference. And then say colon
00:39B2 again. So basically, I'm counting each row, making the first one as a reference. So as it
00:45increases, the number naturally increases in sequential order. If you hit enter, and I'm going
00:51to apply to the rest of the rows by double clicking on this handler here. And you can see all of them
00:57has a formula. Now if I were to apply filter, like for example, I changed my favorite color to only red.
01:03Notice how the index or the serial number adjusts by itself. And if you apply, say, highlight the row
01:10and press Ctrl 9 to hide the row, you can see that the number automatically adjusts, even if you hide the
01:16row. I'm going to undo twice here. We can reorganize the subtotal formula to use function number 103.
01:24But you have to change your cell reference. And you're going to make your row fix. So basically a
01:31counting row. And on this side here, you're going to fix your columns. And you go ahead, enter. And you're
01:35going to apply the same formula to the rest of the row like this. As you can see, that the row index is
01:42changing on the second one range here. So now if you were to apply filter again, say blue this time
01:49around, and then you delete couple of rows or hide, Ctrl 9 to hide it, you can see that the index number
01:54are still in sequential order. The second method of having a sequential index number or serial number
02:02would be to use an aggregate function. So this is how I use an equal aggregate. And the first argument
02:09will be count A here as well. And then comma, five to ignore hidden rows, comma, and then you're going
02:20to use B2 as a reference. I'm going to make it an absolute reference by pressing F4 once like this,
02:28similar to the very first one that we did with subtotal function name number three, colon B2 like this.
02:36And I'm going to close parenthesis and hit enter. I'm going to apply the same formula to the rest of
02:41the row. And you can see that the aggregate function has been applied. Now if you were to apply filter
02:46here, let's say this time around, let's go with only zones here, say north and south. And then you try to
02:52hide a few rows or maybe quite a few of them. Ctrl 9 to hide it, you can see that your index or your serial
03:00number are still in sequential order. Those are the two ways to get sequential serial number.
03:06and then you can see that you'll see that it's before the row. That's the two ways to do it.
03:08You can see that there is now a couple of rows. I'll take that as well.
03:09I'll take that as well. I have to paste it from the other side.
03:10So this is the second row. I have to communicate.
03:12I'll switch this through the row.
03:14So I'm going to close the groove, which is the key.
03:16So I'm going to give you a little bit of a second row.
03:16And you can see that for the year before I see the top row.
03:19So I'm going to close the row.
03:20And if you go ahead and look at the bottom row, take the row across the row.
03:23And if you apply the row.
03:25So I'm going to close the row.
03:27And if you go ahead and look at the row.
Comments