View Single Post
 
Old 08-30-2012, 05:33 AM
macropod's Avatar
macropod macropod is offline Windows 7 64bit Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 22,467
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

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
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote