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]

 

[kweaver]

I wasn't sure how to deal with your blanks, so I put in a helper column C:

=IF(ISBLANK(A2)," ",A2)

Then, I used this formula in B2 and filled down:

=IF(COUNTIF(C$2:C2,C2)=1,MAX(B$1:B1)+1,INDEX(B$2:B2,MATCH(C2,C$2:C2,0),1))

Does that work?