Identifying and counting words

monique · #1 08-29-2012, 03:52 PM

Hi,

I got a word (DOC) file which is filled with the following data:

Quote:

Berlin (Germany)
Long Beach (USA)
Pretoria (South Africa)
Nagoya (Japan)
Shanghai (China)
Singapore (Singapore)
Orlando (USA)
Frankfurt (Germany)
Zhuhai (China)
New Delhi (India)
Munich (Germany)

Washington D.C. (USA)

...and so on. The file consists of almost 40 pages - all the same as in the above example.

Now, what I have to do is to count all cities and all countries. I need to know for example how many times (in total) Germany, USA and India (and all other countries) and how many times Orlando, Beijing and Berlin (and all other cities) are mentioned in the file.

So my question is: how to do this automatically? How to make WORD to count all these mentions of certain countries and cities? And if it' impossible to do in WORD, what other software (preferably from the Microsoft Office package) can I use?

I would be grateful if you could help me. Thanks in advance.

Monique

macropod · #2 08-30-2012, 05:33 AM

Hi Monique,

Try the following macro. It adds two tables after your data, the first containing the country stats, the second containing the city stats:

Code:

Sub Tabulate()
Application.ScreenUpdating = False
Dim Tbl As Table, StrCountries As String, RngSrc As Range, RngDest As Range
Dim i As Long, j As Long, k As Long, l As Long, m As Long, n As Long
With ActiveDocument.Range
  With .Find
    .ClearFormatting
    .Replacement.ClearFormatting
    .Text = " ("
    .Replacement.Text = "^t("
    .Forward = True
    .Wrap = wdFindContinue
    .Format = False
    .MatchCase = False
    .MatchWholeWord = False
    .MatchKashida = False
    .MatchDiacritics = False
    .MatchAlefHamza = False
    .MatchControl = False
    .MatchWildcards = False
    .MatchSoundsLike = False
    .MatchAllWordForms = False
    .Execute Replace:=wdReplaceAll
  End With
  Set Tbl = .ConvertToTable(Separator:=wdSeparateByTabs, _
    NumColumns:=2, AutoFitBehavior:=wdAutoFitFixed)
  With Tbl
    j = .Rows.Count: m = 0
    For i = 1 To j
      Set RngSrc = .Rows(i).Cells(2).Range
      RngSrc.MoveEnd wdCharacter, -1
      If InStr(StrCountries, RngSrc.Text) = 0 Then
        StrCountries = StrCountries & "|" & RngSrc.Text
        .Rows.Add
        k = k + 1
        .Rows(j + k).Cells(1).Range.Text = RngSrc.Text
        .Rows(j + k).Cells(2).Range.Text = "1"
      Else
        l = UBound(Split(Split(StrCountries, RngSrc.Text)(0), "|")) + m
        Set RngDest = .Rows(j + l).Cells(2).Range
        RngDest.MoveEnd wdCharacter, -1
        RngDest.Text = CStr(CLng(RngDest.Text) + 1)
      End If
      .Rows(i).Cells.Merge
    Next
    StrCountries = "": m = k: n = k
    For i = 1 To j
      Set RngSrc = .Rows(i).Cells(1).Range
      RngSrc.MoveEnd wdCharacter, -1
      RngSrc.Text = Replace(RngSrc.Text, vbCr, " ")
      If InStr(StrCountries, RngSrc.Text) = 0 Then
        StrCountries = StrCountries & "|" & RngSrc.Text
        .Rows.Add
        k = k + 1
        .Rows(j + k).Cells(1).Range.Text = RngSrc.Text
        .Rows(j + k).Cells(2).Range.Text = "1"
      Else
        l = UBound(Split(Split(StrCountries, RngSrc.Text)(0), "|")) + m
        Set RngDest = .Rows(j + l).Cells(2).Range
        RngDest.MoveEnd wdCharacter, -1
        RngDest.Text = CStr(CLng(RngDest.Text) + 1)
      End If
    Next
    .Split j + 1
    .ConvertToText Separator:=vbCr
  End With
  .Tables(1).Split n + 1
End With
Set RngSrc = Nothing: Set RngDest = Nothing
Application.ScreenUpdating = True
End Sub

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Why Words doesn’t show the style of the selected words automatically????	Jamal NUMAN	Word	0	04-14-2011 03:20 PM
Counting Colors	g48dd	Excel	2	03-13-2011 09:28 PM
listing and counting words	gencoglux	Word	2	05-07-2010 05:10 PM
Counting Legal Words	bulletrick	Word	0	12-30-2005 03:22 AM
Identifying the Dynamic Connector	cyberbhai	Misc	0	12-14-2005 03:55 AM