x
Solved

# VBA Code

Posted on 2014-04-30
Medium Priority
159 Views
Hi guys,

Attached you will find a sample of what the data is and the desirable result if it is possible.
Thank a lot,
Example.xls
0
Question by:marian68
• 2
• 2

LVL 25

Accepted Solution

Ejgil Hedegaard earned 1000 total points
ID: 40032827
Formula in B18, copy down, see file

=B2&IF(COUNTIF(\$B\$2:\$B\$10,B2)>1,"("&COUNTIF(\$B\$2:B2,B2)&")","")
Example-word-count.xls
0

Author Comment

ID: 40032837
Thank you,

The formule will work even for 10000 records?
0

LVL 43

Assisted Solution

Saqib Husain, Syed earned 1000 total points
ID: 40032858
Try this macro

Sub wordnums()
Dim ws As Worksheet
Dim lcel As Range
Dim scel As Range
Dim cel As Range
Dim wrd As String
Dim ctr As Long
Set ws = ActiveSheet
Set lcel = Range("B1").End(xlDown)
For Each cel In Range("B2", lcel)
If WorksheetFunction.CountIf(cel.EntireColumn, cel) > 1 Then
wrd = cel.Value
ctr = 1
For Each scel In Range(cel, lcel)
If wrd = scel Then
scel.Value = scel & "(" & ctr & ")"
ctr = ctr + 1
End If
Next scel
End If
Next cel
End Sub
0

Author Closing Comment

ID: 40033045
Thank you guys.
0

LVL 25

Expert Comment

ID: 40033065
The formula will work for 10000 records, but will take a while to calculate.
My test took 1 minute to copy and calculate, so when done, I would leave the formula for the first record, and convert the rest to values.
With the formula at the first record, recalculation can always be done again.
0

## Featured Post

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.