Assign the Same Sequential Numbers to Duplicates

[paynod]

I'm using Excel 2007. I'm trying to reacreate the pattern shown in the sequence column. Could someone please give me a pointer as to how I could do this?

 

[paynod]

It looks like it didn't upload the image so here it is again.

 

[RoryA]

Can you sort the data on the first column? It makes the formula much simpler.

 

[paynod]

Can you sort the data on the first column? It makes the formula much simpler.

Agreed, it would be much easier. Unfortunately, this is part of a much larger data set and I'm unable to sort the data.

 

[RoryA]

In that case I think you're stuck with something like this (assumes data in A from row 2, and formulas in B):

=IF(A2=A1,B1,IFERROR(VLOOKUP(A2,A$1:B1,2,0),max(B$1:B1)+1))

 

[paynod]

In that case I think you're stuck with something like this (assumes data in A from row 2, and formulas in B):

=IF(A2=A1,B1,IFERROR(VLOOKUP(A2,A$1:B1,2,0),max(B$1:B1)+1))

Thanks Rory. That's brilliant. I don't understand the VLOOKUP element within that function. Could you explain what's happening there?..

 

[RoryA]

It's looking up the name in column A in all the rows above the current row to return any number that had already been assigned to that dataset. If that fails, it then assigns the next number in sequence.

 

[paynod]

Thanks Rory, I've realised it's not quite going to do what I need to acheive. In hindsight, the sequence needs to be reset when a product (T101,T102,T103 etc) changes. I've changed the example data to reflect this in the attachment below.

 

[RoryA]

Sorry, I'm not following. Is that an example of what it should look like? If so, does that mean that the part before the hyphen is irrelevant?

 

[paynod]

Sorry, I'm not following. Is that an example of what it should look like? If so, does that mean that the part before the hyphen is irrelevant?

The part before the hyphen is completely relevent. The product and data column is a concatenated string of a product and an attribute. The part before the hyphen is the product code and the part after is it's attribute. We can have the same attribute but for different products like in the example. I'm trying to create a count of the number of instances of each product attribute but only if the attribute changes. This count of each attribute will reset when the product code changes. I've altered the illustration to make it more clear. The data highlighted in green is the desired result. Thanks again for your help.