Hi Heather,
see
http://word.mvps.org/faqs/interdev/ControlXLFromWord.htm
as a start.
Also if the client listing has the same person with 2 different values
I am supposed to just list the 2 different values.
Would need some programming. Preferably done in Excel.
Sort the Excel sheet according to the names.
Look if a cell with name "Meier" is followed
by another cell in the same column
containing the same name. If so, move the value
from the row with the found name duplicate up one
row and append it to the value in the row with the
first occurence of the name "Meier". Delete the
current row. Repeat until a name different of "Meier"
is in the second (next) row. Detract 1 from the number
of all rows. I can't describe all details.
Like
Input:
Name Value
Meier 1
Meier 2
Meier 3
Output
Name Value
Meier 1, 2 and 3
Not quite simple, though.
This is an example from print magazine production.
There is a list, containing names and the numbers of
all the pages, where this name occurs.
It produces an output like:
Meier was found on page 13.
or
Meier was found on pages 13 and 14.
or
Meier was found on pages 13, 14 and 17.
It is working, though I coded it long time ago,
originally in German, and didn't comment it for publishing,
unfortunately.
Good luck.
Sub SortIt()
Dim intRow As Integer
Dim intCol As Integer
Dim strTmp As Variant
Dim NewVal As Variant
Dim strA As String
Dim strB As String
Dim i, j, k As Integer
Dim PosKom As Integer ' position of comma
' ----------------------------------- set tmp08 to 0
Cells(2, 3).Select
If Left(Selection.Text, 1) = "d" Then Exit Sub ' ???
intRow = 2
intCol = 2
Cells(intRow, intCol).Select
While Cells(intRow, intCol).Value <> ""
Cells(intRow, intCol).Value = 0
intRow = intRow + 1
Wend
intRow = 1
intCol = 1
i = 1
' GoTo skipsort
' ---------------------------------------- sort
Cells.Select
Selection.Sort _
Key1:=Range("A2"), _
Order1:=xlAscending, _
Key2:=Range("C2"), _
Order2:=xlAscending, _
Header:=xlYes, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom
skipsort:
' ------------------- count number of rows to be processed
Cells(intRow, 1).Select
While Cells(intRow, 1).Value <> ""
intRow = intRow + 1
Wend
intRow = intRow - 1
For i = intRow To 3 Step -1
strA = Cells(i, 1).Value
strB = Cells(i - 1, 1).Value
If strA = strB Then
strTmp = Cells(i - 1, 3).Value
strTmp = strTmp & ", "
Cells(i, 3).Select
strTmp = strTmp & Cells(i, 3).Value
Cells(i, 3).Value = strTmp
Rows(i - 1).Select
Selection.Delete Shift:=xlUp
End If
Next
' -------------------------------------------------- new count
intRow = 1
Cells(intRow, 1).Select
While Cells(intRow, 1).Value <> ""
intRow = intRow + 1
Wend
intRow = intRow - 1
For i = 2 To intRow
strTmp = Cells(i, 3).Value
k = 1
For j = 1 To Len(strTmp)
If Mid(strTmp, j, 1) = "," Then
k = k + 1
End If
Next
Cells(i, 2).Value = k
Next
For i = 2 To intRow
If Cells(i, 2).Value > 1 Then
strTmp = Cells(i, 3).Value
PosKom = 0
For j = 1 To Len(strTmp)
If Mid(strTmp, j, 1) = "," Then
PosKom = j
End If
Next
' Cells(i, 3).Select
' MsgBox "Position = " & PosKom
NewVal = Left(strTmp, PosKom - 1)
NewVal = NewVal & " and"
NewVal = NewVal & Right(strTmp, Len(strTmp) - PosKom)
NewVal = "the articles on the pages " & NewVal
Cells(i, 3).Value = NewVal
End If
If Cells(i, 2).Value = 1 Then
strTmp = Cells(i, 3).Value
strTmp = "the article on the page " & strTmp
Cells(i, 3).Value = strTmp
End If
Next
Exit Sub
For i = 2 To intRow
If Cells(i, 2).Value > 1 Then
' pages k = k + 1
Else
' pages
End If
Next
End Sub
Greetings from Bavaria, Germany
Helmut Weber, MVP
"red.sys" & chr(64) & "t-online.de"
Word XP, Win 98
http://word.mvps.org/