[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 158
  • Last Modified:

VBA Code

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
marian68
Asked:
marian68
  • 2
  • 2
2 Solutions
 
Ejgil HedegaardCommented:
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
 
marian68Author Commented:
Thank you,

The formule will work even for 10000 records?
0
 
Saqib Husain, SyedEngineerCommented:
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
 
marian68Author Commented:
Thank you guys.
0
 
Ejgil HedegaardCommented:
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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now