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:

<tbody></tbody>

Bob

Bob

Bob

Bob

Bob

Bob

Bob

Bob

Bob

Bob

Bob

Bob

Bob

Tony

Tony

Tony

Tony

Tony

Tony

Tony

Tony

Tony

Tony

Tony

Tony

Sue

Sue

Sue

Sue

Sue

Sue

<colgroup><col style="width:48pt" width="64"> </colgroup><tbody>
</tbody>

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

<tbody>

Bob

Bob

Bob

Bob

Bob

Bob - 2

Bob - 2

Bob - 2

Bob - 2

Bob - 2

Bob - 3

Bob - 3

Bob - 3

Tony

Tony

Tony

Tony

Tony

Tony - 2

Tony - 2

Tony - 2

Tony - 2

Tony - 2

Tony - 3

Tony - 3

Sue

Sue

Sue

Sue

Sue

Sue - 2

<colgroup><col style="width:48pt" width="64"> </colgroup><tbody>
</tbody>

</tbody>

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:

Bob

Bob

Bob

Bob

Bob

Bob - 2

Bob - 2

Bob - 2

Bob - 2

Bob - 2

Bob - 3

Bob - 3

Bob - 3

Tony

Tony

Tony

Tony

Tony

Tony - 2

Tony - 2

Tony - 2

Tony - 2

Tony - 2

Tony - 3

Tony - 3

Sue

Sue

Sue

Sue

Sue

Sue - 2

<tbody>
</tbody>

 

[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)