Rename Duplicates

[jimmytiler]

I have a list of town names for each state. However, some states have duplicate towns with different post-codes. Is it possible to search for duplicates then have them renamed in order: e.g. AliceSprings (1), AliceSprings (2), AliceSprings (3) ..... and so on? *I will need the numbers in parenthesis at the end due to an autocompletion set up for "SUBURB" in another cell.

 

[Max1616]

Try a formula similar to this, where Column H contains the Suburbs (Assuming your data is in a table):

=IF(COUNTIF([Suburb],[@Suburb])>1,[@Suburb]&" (" & COUNTIF($H$2:H2,[@Suburb]) & ")",[@Suburb])

 

[Max1616]

Here's the formula if you'd only like the cell references instead of the table field names:

=IF(COUNTIF($H:$H,H2)>1,H2&" (" & COUNTIF($H$2:H2,H2) & ")",H2)

 

[jimmytiler]

WOW! That worked. Thank you so much Max1616. I must admit though, I don't understand how it works (but so glad it does)

 

[Max1616]

Glad I could help!