Renaming every Nth repeated value using macro!

[Peter Selwin]

Hello guys,

Can anyone think of a way to add a value to every Nth cell that has a repeating value?

Example:
[TABLE="width: 500"]
<tbody></tbody>[/TABLE]
[TABLE="width: 64"]
<colgroup><col style="width:48pt" width="64"> </colgroup><tbody>[TR]
[TD="width: 64"]Bob
[/TD]
[/TR]
[TR]
[TD]Bob[/TD]
[/TR]
[TR]
[TD]Bob[/TD]
[/TR]
[TR]
[TD]Bob
[/TD]
[/TR]
[TR]
[TD]Bob[/TD]
[/TR]
[TR]
[TD]Bob[/TD]
[/TR]
[TR]
[TD]Bob[/TD]
[/TR]
[TR]
[TD]Bob[/TD]
[/TR]
[TR]
[TD]Bob[/TD]
[/TR]
[TR]
[TD]Bob[/TD]
[/TR]
[TR]
[TD]Bob[/TD]
[/TR]
[TR]
[TD]Bob[/TD]
[/TR]
[TR]
[TD]Bob[/TD]
[/TR]
[TR]
[TD]Tony[/TD]
[/TR]
[TR]
[TD]Tony[/TD]
[/TR]
[TR]
[TD]Tony[/TD]
[/TR]
[TR]
[TD]Tony[/TD]
[/TR]
[TR]
[TD]Tony[/TD]
[/TR]
[TR]
[TD]Tony[/TD]
[/TR]
[TR]
[TD]Tony[/TD]
[/TR]
[TR]
[TD]Tony[/TD]
[/TR]
[TR]
[TD]Tony[/TD]
[/TR]
[TR]
[TD]Tony[/TD]
[/TR]
[TR]
[TD]Tony[/TD]
[/TR]
[TR]
[TD]Tony[/TD]
[/TR]
[TR]
[TD]Sue[/TD]
[/TR]
[TR]
[TD]Sue[/TD]
[/TR]
[TR]
[TD]Sue[/TD]
[/TR]
[TR]
[TD]Sue
[/TD]
[/TR]
[TR]
[TD]Sue
[/TD]
[/TR]
[TR]
[TD]Sue
[/TD]
[/TR]
</tbody>[/TABLE]

Every 5th value in the column that has the same value would be renamed with a number when a macro is used:

[TABLE="width: 500"]
<tbody>[TABLE="width: 64"]
<colgroup><col style="width:48pt" width="64"> </colgroup><tbody>[TR]
[TD="width: 64"]Bob[/TD]
[/TR]
[TR]
[TD]Bob[/TD]
[/TR]
[TR]
[TD]Bob[/TD]
[/TR]
[TR]
[TD]Bob[/TD]
[/TR]
[TR]
[TD]Bob[/TD]
[/TR]
[TR]
[TD]Bob - 2[/TD]
[/TR]
[TR]
[TD]Bob - 2[/TD]
[/TR]
[TR]
[TD]Bob - 2[/TD]
[/TR]
[TR]
[TD]Bob - 2[/TD]
[/TR]
[TR]
[TD]Bob - 2[/TD]
[/TR]
[TR]
[TD]Bob - 3[/TD]
[/TR]
[TR]
[TD]Bob - 3[/TD]
[/TR]
[TR]
[TD]Bob - 3[/TD]
[/TR]
[TR]
[TD]Tony[/TD]
[/TR]
[TR]
[TD]Tony[/TD]
[/TR]
[TR]
[TD]Tony[/TD]
[/TR]
[TR]
[TD]Tony[/TD]
[/TR]
[TR]
[TD]Tony[/TD]
[/TR]
[TR]
[TD]Tony - 2[/TD]
[/TR]
[TR]
[TD]Tony - 2[/TD]
[/TR]
[TR]
[TD]Tony - 2[/TD]
[/TR]
[TR]
[TD]Tony - 2[/TD]
[/TR]
[TR]
[TD]Tony - 2[/TD]
[/TR]
[TR]
[TD]Tony - 3[/TD]
[/TR]
[TR]
[TD]Tony - 3[/TD]
[/TR]
[TR]
[TD]Sue[/TD]
[/TR]
[TR]
[TD]Sue[/TD]
[/TR]
[TR]
[TD]Sue
[/TD]
[/TR]
[TR]
[TD]Sue[/TD]
[/TR]
[TR]
[TD]Sue[/TD]
[/TR]
[TR]
[TD]Sue - 2
[/TD]
[/TR]
</tbody>[/TABLE]
</tbody>[/TABLE]

I'm puzzled on this one!

Thanks!

 

[Peter_SSs]

Welcome to the MrExcel board!

Could you now show us what the result should look like for that sample data?

 

[Peter Selwin]

That's so strange, I posted what it should look like but it didn't show up!

Here are the intended results:

[TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65, width: 64"]Bob[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64"]Bob[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64"]Bob[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64"]Bob[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64"]Bob[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64"]Bob - 2[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64"]Bob - 2[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64"]Bob - 2[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64"]Bob - 2[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64"]Bob - 2[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64"]Bob - 3[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64"]Bob - 3[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64"]Bob - 3[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64"]Tony[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64"]Tony[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64"]Tony[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64"]Tony[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64"]Tony[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64"]Tony - 2[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64"]Tony - 2[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64"]Tony - 2[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64"]Tony - 2[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64"]Tony - 2[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64"]Tony - 3[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64"]Tony - 3[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64"]Sue[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64"]Sue[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64"]Sue[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64"]Sue[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64"]Sue[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64"]Sue - 2[/TD]
[/TR]
</tbody>[/TABLE]

 

[Peter Selwin]

I have tried doing this through formulas in adjacent cells but no luck.

I can get a count added to all values that are the same: =A1&"-" &COUNTIF($A$1:A1,A1)

and I can pull the value of every 5th row: =OFFSET($A$1,(ROW()-1)*5,0)

I just can't figure out how to get every 5 rows of the same value to be renamed either through formula or VBA.

 

[Rick Rothstein]

Give this formula a try...

=SUBSTITUTE(A1&" - "&1+INT((COUNTIF(A$1:A1,A1)-1)/5)," - 1","")

 

[Peter Selwin]

Hi Rick,

Thanks so much for your help, I've been trying to figure this out for a few days now!

I had tried a similar method with SUBSTITUTE but had the COUNTIF and INT backwards, this cleared my head.

Much appreciated again.

 

[Peter_SSs]

It may not be possible with your data, but that formula will fail if you have more than 45 occurrences of a name.
This alternative does not suffer that fate.

=A1&IF(COUNTIF(A$1:A1,A1)<6,""," - "&INT((COUNTIF(A$1:A1,A1)-1)/5)+1)