Solved

VBA Code

Posted on 2014-04-30
5
154 Views
Last Modified: 2014-04-30
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
Comment
Question by:marian68
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 22

Accepted Solution

by:
Ejgil Hedegaard earned 250 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

by:marian68
ID: 40032837
Thank you,

The formule will work even for 10000 records?
0
 
LVL 43

Assisted Solution

by:Saqib Husain, Syed
Saqib Husain, Syed earned 250 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

by:marian68
ID: 40033045
Thank you guys.
0
 
LVL 22

Expert Comment

by:Ejgil Hedegaard
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

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

623 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question