Identify blocks of the same value in a sequence with unique identifier

[dgerding]

Hi all -

I'm trying to randomly generate a unique identifier for each block of the same value in a column. In my mind, it's something that searches for "groups" or "blocks" of records in column A and then assigned a sequential identifier in column B. I'm not picky about the identifier, I just need each "block" to be unique.

Is this possible without writing a macro? I've attached an example below. I have over 40,000 rows so I really need to automate this process. Thanks in advance!

[TABLE="width: 139"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Value[/TD]
[TD]Unique Sequence Identifier[/TD]
[/TR]
[TR]
[TD]YES[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]YES[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]YES[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]NO[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]NO[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]NO[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]NO[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]NO[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]NO[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]NO[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]NO[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]NO[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]YES[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]YES[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]YES[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]YES[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]YES[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]YES[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]YES[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]NO[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]NO[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]NO[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]NO[/TD]
[TD="align: right"]4[/TD]
[/TR]
</tbody>[/TABLE]

 

[pbornemeier]

Welcome to the Board!

If you put a 1 in B2, then put this formula in B3 and copy it down you will get what you are asking for:

=IF(A3=A2,B2,B2+1)

 

[dgerding]

This is perfect - and so simple! Thanks!