Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

VBA Code

Posted on 2014-04-30
5
Medium Priority
?
157 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
  • 2
  • 2
5 Comments
 
LVL 23

Accepted Solution

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

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

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

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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

876 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