Just to point out a couple of additional things. The code treats upper case
letters separately from lower case letters; plus if handles punctuation
marks as well. Also, if you leave the cell that contains the old message
blank, the code will give you a count of each character used in the
message... this might come in handy if you are worried that your message may
be using more of one character than you have physically letters for and you
want to check this out.

--
Rick (MVP - Excel)


"Rick Rothstein" wrote in message
...
If I did everything correctly (and I think I did<g), the macro below
should create two lists for you... one telling you which characters, and
how many of them, need to be removed from the sign board; and a second one
telling which characters, and how many of them, need to be added to the
sign board... those letters common to both messages will be left on the
sign board. To install the macro, press Alt+F11 to go into the VB editor,
click Insert/Module from its menu bar, and then copy/paste the macro into
the code window that appears. To use, first change the four Const
statements to reflect your actual set up. I have assume in my example
Const statements that the worksheet name is Sheet2, the text for the old
(existing) message is in A1, the text for the new message is in A2 and the
first of the output lists will start in A4 and the second list will start
in the cell below that one (and the lists will be entered into individual
cells, from left to right, cell by cell, along each list's row).

Sub LetterNeededForSignChange()
Dim X As Long
Dim OldMessage As String
Dim NewMessage As String
Dim Add() As String
Dim Remove() As String
Dim OldLetters(32 To 126) As Long
Dim NewLetters(32 To 126) As Long

Const SheetName As String = "Sheet2"
Const OldMessageCell As String = "A1"
Const NewMessageCell As String = "A2"
Const OutputCell As String = "A4"

ReDim Add(0)
ReDim Remove(0)
Add(0) = "Add ="
Remove(0) = "Remove ="
With Worksheets(SheetName)
OldMessage = .Range(OldMessageCell).Value
NewMessage = .Range(NewMessageCell).Value
For X = 1 To Len(OldMessage)
OldLetters(Asc(Mid(OldMessage, X, 1))) = _
OldLetters(Asc(Mid(OldMessage, X, 1))) + 1
Next
For X = 1 To Len(NewMessage)
NewLetters(Asc(Mid(NewMessage, X, 1))) = _
NewLetters(Asc(Mid(NewMessage, X, 1))) + 1
Next
For X = 33 To 126
If NewLetters(X) < OldLetters(X) Then
ReDim Preserve Remove(UBound(Remove) + 1)
Remove(UBound(Remove)) = Abs(NewLetters(X) - OldLetters(X)) & _
"-" & Chr(X)
ElseIf NewLetters(X) OldLetters(X) Then
ReDim Preserve Add(UBound(Add) + 1)
Add(UBound(Add)) = (NewLetters(X) - OldLetters(X)) & _
"-" & Chr(X)
End If
Next
For X = 0 To UBound(Remove)
.Range(OutputCell).Offset(, X).Value = Remove(X)
Next
For X = 0 To UBound(Add)
.Range(OutputCell).Offset(1, X).Value = Add(X)
Next
End With
End Sub

--
Rick (MVP - Excel)


"Bajohn56345" wrote in message
...
I am looking for a way to use excel in changing the wording on the sign in
front of our church building. Every week we put up a new message. I pull
the
letters for the person that changes the sign. I have to account for the
letters that are on the sign, pull the new letters needed while leaving
the
ones that we will use again on the sign. There must be a way that I can
quickly use excel to tell me what new letters that I need.