Numbering Order

[happyhungarian]

Hi, I'm having difficulty getting a numbering scheme working correctly. I have a list of descriptions that I want to assign a number. If a certain description is used one time the number that gets assigned needs to be the same number that is used if that description shows up again later down the line. The following is an example description set, the formula used, the resulting "Numbering", and the issue highlighted. I need to do this all within the same column/cell but the problem is that this number is a subset of additional numbers so I can't have a trailing Vlookup to see if that description popped up before (because it will lookup the entire number not just this subset). I also can't have a separate reference section for a vlookup (which I was originally going to do).


[TABLE="width: 915"]
<tbody>[TR]
[TD]Description[/TD]
[TD]Numbering[/TD]
[TD]Formula for "Numbering"[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]1[/TD]
[TD]SUMPRODUCT(1/COUNTIF(B$3:B3,B$3:B3&""))[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]2[/TD]
[TD]SUMPRODUCT(1/COUNTIF(B$3:B4,B$3:B4&""))[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]3[/TD]
[TD]SUMPRODUCT(1/COUNTIF(B$3:B5,B$3:B5&""))[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]3[/TD]
[TD]SUMPRODUCT(1/COUNTIF(B$3:B6,B$3:B6&""))[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]3[/TD]
[TD]SUMPRODUCT(1/COUNTIF(B$3:B7,B$3:B7&""))[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]4[/TD]
[TD]SUMPRODUCT(1/COUNTIF(B$3:B8,B$3:B8&""))[/TD]
[TD]<---"Blanks" accurately counted as a separate number[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]4[/TD]
[TD]SUMPRODUCT(1/COUNTIF(B$3:B9,B$3:B9&""))[/TD]
[TD]<--- this should be "3"[/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD]5[/TD]
[TD]SUMPRODUCT(1/COUNTIF(B$3:B10,B$3:B10&""))[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]E[/TD]
[TD]6[/TD]
[TD]SUMPRODUCT(1/COUNTIF(B$3:B11,B$3:B11&""))[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]6[/TD]
[TD]SUMPRODUCT(1/COUNTIF(B$3:B12,B$3:B12&""))[/TD]
[TD]<--- this should be "3"[/TD]
[/TR]
</tbody>[/TABLE]

 

[shg]

[Table="width:, class:grid"][tr][td="bgcolor:#C0C0C0"][/td][td="bgcolor:#C0C0C0"]

B​

[/td][td="bgcolor:#C0C0C0"]

C​

[/td][td="bgcolor:#C0C0C0"]

D​

[/td][/tr][tr][td="bgcolor:#C0C0C0"]

2​

[/td][td="bgcolor:#F3F3F3"]

Description​

[/td][td="bgcolor:#F3F3F3"]

Numbering​

[/td][td="bgcolor:#F3F3F3"]

​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

3​

[/td][td]A[/td][td]

1​

[/td][td]C3: =IF(COUNTIF(B$2:B2, B3), VLOOKUP(B3, $B$2:C2, 2, FALSE), SUMPRODUCT(1 / COUNTIF(B$3:B3, B$3:B3 & "")))[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

4​

[/td][td]B[/td][td]

2​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

5​

[/td][td]C[/td][td]

3​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

6​

[/td][td]C[/td][td]

3​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

7​

[/td][td]C[/td][td]

3​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

8​

[/td][td][/td][td]

4​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

9​

[/td][td]C[/td][td]

3​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

10​

[/td][td]D[/td][td]

5​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

11​

[/td][td]E[/td][td]

6​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

12​

[/td][td]C[/td][td]

3​

[/td][td][/td][/tr]
[/table]

 

[happyhungarian]

So I came up with a trailing Vlookup with a find function. The number I'm having difficult with that I stated above is the second digit in the sequence. Here's the formula I used to calculate the first "1.1.1" sequence:

=A$1&"."&IFERROR(SUBSTITUTE(MID(SUBSTITUTE("." & VLOOKUP(B3,B$2:C2,2,FALSE)&REPT(" ",6),".",REPT(".",255)),2*255,255),".",""),SUMPRODUCT(1/COUNTIF(B$3:B3,B$3:B3&"")))&"."&COUNTIF($B$3:B3,B3)


Now the only problem I'm having is on BLANKS. The last row (1.6.0) should really be 1.4.1 since there was a preceeding BLANK. How can I change this to count blanks?



[TABLE="width: 427"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]Description[/TD]
[TD]Numbering[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]1.1.1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]1.2.1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]1.3.1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]1.3.2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]1.3.3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]1.4.0[/TD]
[TD]<---Accurately counts first blank as "4"[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]1.3.4[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD]1.5.1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]E[/TD]
[TD]1.6.1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]1.3.5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]1.6.0[/TD]
[TD]<--- Should not be "6" should be "4"[/TD]
[/TR]
</tbody>[/TABLE]

 

[happyhungarian]

@shg thanks! That's the same solution I was thinking of but the only problem is the handling of blanks... it doesn't capture them correctly. It either needs to be "4" OR counted as it's own number (7) and then anything after that would see that there are two separate blanks.

 

[happyhungarian]

@marcelo branco... thanks but it looks like the formula errors out if there are two blanks... if you copy the formula into C13 it'll error rather than either assigning a new number or using the "4" from the previous blank

 

[Marcelo Branco]

Maybe...


[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

B​

[/TD]
[TD="bgcolor: #DCE6F1"]

C​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2​

[/TD]
[TD]

Description​

[/TD]
[TD]

Numbering​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

3​

[/TD]
[TD]

A​

[/TD]
[TD]

1​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

4​

[/TD]
[TD]

B​

[/TD]
[TD]

2​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

5​

[/TD]
[TD]

C​

[/TD]
[TD]

3​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

6​

[/TD]
[TD]

C​

[/TD]
[TD]

3​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

7​

[/TD]
[TD]

C​

[/TD]
[TD]

3​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

8​

[/TD]
[TD][/TD]
[TD]

4​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

9​

[/TD]
[TD]

C​

[/TD]
[TD]

3​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

10​

[/TD]
[TD]

D​

[/TD]
[TD]

5​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

11​

[/TD]
[TD]

E​

[/TD]
[TD]

6​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

12​

[/TD]
[TD]

C​

[/TD]
[TD]

3​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

13​

[/TD]
[TD][/TD]
[TD]

4​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

14​

[/TD]
[TD]

D​

[/TD]
[TD]

5​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

15​

[/TD]
[TD]

E​

[/TD]
[TD]

6​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

16​

[/TD]
[TD][/TD]
[TD]

4​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

17​

[/TD]
[TD]

F​

[/TD]
[TD]

7​

[/TD]
[/TR]
</tbody>[/TABLE]


EDIT
Formula in C3 copied down
=IF(COUNTIF(B$3:B3,B3&"")=1,SUMPRODUCT(1/COUNTIF(B$3:B3,B$3:B3&"")),INDEX(C$2:C2,MATCH(B3&"",INDEX(B$2:B2&"",),0)))

M.

 

[happyhungarian]

For some reason when I enter it i still get an error on a second blank but your result on your table looks like it works perfect

 

[Marcelo Branco]

For some reason when I enter it i still get an error on a second blank

I edited the formula - try again.

M.

 

[happyhungarian]

that did it!! thank you!

 

[Marcelo Branco]

You're welcome. Thanks for the feedback.

M.