Dynamic Numbering

[lawi227]

I have a list of to-do's and want to number them based on grouping. I would think this would be a fairly easy formula to build but my knowledge of Excel is limited.


Below is an example of WHAT I WOULD LIKE (row 1 is the column this data can be found in; row 2 is the name of the column; row 3 begins sample data):
[TABLE="width: 500"]
<tbody>[TR]
[TD]Column B[/TD]
[TD]Column G[/TD]
[/TR]
[TR]
[TD]"#"[/TD]
[TD]"To-Do"[/TD]
[/TR]
[TR]
[TD]01.01.1[/TD]
[TD]01.01 Clean House[/TD]
[/TR]
[TR]
[TD]01.01.2[/TD]
[TD]01.01 Clean House[/TD]
[/TR]
[TR]
[TD]01.02.1[/TD]
[TD]01.02 Wash Carpet[/TD]
[/TR]
[TR]
[TD]01.02.2[/TD]
[TD]01.02 Wash Carpet[/TD]
[/TR]
[TR]
[TD]01.02.3[/TD]
[TD]01.02 Wash Carpet[/TD]
[/TR]
[TR]
[TD]02.01.1[/TD]
[TD]02.01 Clean Car[/TD]
[/TR]
[TR]
[TD]02.02.1[/TD]
[TD]02.02 Mow Lawn[/TD]
[/TR]
[TR]
[TD]02.02.2[/TD]
[TD]02.02 Mow Lawn[/TD]
[/TR]
</tbody>[/TABLE]

Note: I may add a to-do to the bottom of the list; e.g., 01.01 may end up in row 50 while all the other "01.01 to-dos" are in rows 3:4. Therefore the formula cannot just check to see if the cell in column G above is the same.

I built the first part of the formula which is to extract the first 5 characters from the ### column G: =left(G2,5). I would then like to add the unique number after those 5 characters extracted (as shown above in the table).

I need help building the rest of the formula.

Thanks so much!

 

[Scott Huish]

In B2:
=LEFT(G2,FIND(" ",G2&" ")-1)&"."&COUNTIF($G$2:G2,LEFT(G2,FIND(" ",G2&" ")-1)&"*")

Copy down.

Excel 2010
	B	C	D	E	F	G
2	01.01.1					01.01 Clean House
3	01.01.2					01.01 Clean House
4	01.02.1					01.02 Wash Carpet
5	01.02.2					01.02 Wash Carpet
6	01.02.3					01.02 Wash Carpet
7	02.01.1					02.01 Clean Car
8	02.02.1					02.02 Mow Lawn
9	02.02.2					02.02 Mow Lawn
10	01.01.3					01.01 Clean House
Sheet1

Cell Formulas
Range	Formula
B2	=LEFT(G2,FIND(" ",G2&" ")-1)&"."&COUNTIF($G$2:G2,LEFT(G2,FIND(" ",G2&" ")-1)&"*")

 

[lawi227]

In B2:
=LEFT(G2,FIND(" ",G2&" ")-1)&"."&COUNTIF($G$2:G2,LEFT(G2,FIND(" ",G2&" ")-1)&"*")

Copy down.

Excel 2010

	B	C	D	E	F	G
01.01.1	01.01 Clean House
01.01.2	01.01 Clean House
01.02.1	01.02 Wash Carpet
01.02.2	01.02 Wash Carpet
01.02.3	01.02 Wash Carpet
02.01.1	02.01 Clean Car
02.02.1	02.02 Mow Lawn
02.02.2	02.02 Mow Lawn
01.01.3	01.01 Clean House

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]2[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]B2[/TH]
[TD="align: left"]=LEFT(G2,FIND(" ",G2&" ")-1)&"."&COUNTIF($G$2:G2,LEFT(G2,FIND(" ",G2&" ")-1)&"*")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

This worked perfect! Thanks so much. Can you explain briefly how it works? I am unfamiliar with the FIND function.

 

[Scott Huish]

Sure.

We need to isolate the number part by itself. So FIND looks for the first space and returns the position of it. We subtract 1 because we don't actually want the space.
So, in this line:
01.01 Clean House

The Space is at position 6. We subtract 1 and then take this many characters with LEFT leaving 01.01. Then we want to do a COUNTIF to get a count of everything that starts with that at that point in the list which is why it is an absolute reference in the first part of the range in the COUNTIF but the second part of the range is allowed to increase by row. So it only gives you the count up to that point, which is why you're numbers will increase the further it goes down the list.

Hope this helps

 

[lawi227]

Thanks so much!

Can I ask for a slight modification if it's easy....

The numbering you created after the "01.01" doesn't begin with a zero. Is there anyway to adjust the formula so it displays "01.01.01" instead of "01.01.1"?

 

[Scott Huish]

It didn't start with a zero because you didn't show that in your original sample.

Here you go:
=LEFT(G2,FIND(" ",G2&" ")-1)&TEXT(COUNTIF($G$2:G2,LEFT(G2,FIND(" ",G2&" ")-1)&"*"),"\.00")
Copy down.

 

[lawi227]

Hi Scott,

I am trying to modify the above formula. Whenever there is a blank in column G, can the formula just number those blanks 1, 2, 3, 4, etc. - no need to add the ".00". This numbering would ideally be a continuation so that all blanks (Regardless of row), will be numbered.

 

[Scott Huish]

=if(g2="",countif($g$2:g2,""),left(g2,find(" ",g2&" ")-1)&text(countif($g$2:g2,left(g2,find(" ",g2&" ")-1)&"*"),"\.00"))