This compares two URLs folder-to-folder (items separated by forward slashes). This is similar to the WORDDIF function above.
I only tested it with the example you gave. Let me know how well it works.
Code:[COLOR=darkblue]Function[/COLOR] URLDIF(rngA [COLOR=darkblue]As[/COLOR] Range, rngB [COLOR=darkblue]As[/COLOR] Range) [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR] [COLOR=darkblue]Dim[/COLOR] WordsA [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Variant[/COLOR], WordsB [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Variant[/COLOR] [COLOR=darkblue]Dim[/COLOR] ndxA [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR], bJoin [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Boolean[/COLOR], strTemp As [COLOR=darkblue]String[/COLOR] WordsA = Split(rngA.Text, "/") WordsB = Split(rngB.Text, "/") [COLOR=darkblue]For[/COLOR] ndxA = [COLOR=darkblue]LBound[/COLOR](WordsA) [COLOR=darkblue]To[/COLOR] [COLOR=darkblue]UBound[/COLOR](WordsA) [COLOR=darkblue]If[/COLOR] [COLOR=darkblue]UBound[/COLOR](WordsB) < ndxA [COLOR=darkblue]Then[/COLOR] [COLOR=darkblue]Exit[/COLOR] [COLOR=darkblue]For[/COLOR] [COLOR=darkblue]If[/COLOR] [COLOR=darkblue]StrComp[/COLOR](WordsA(ndxA), WordsB(ndxA), vbTextCompare) = 0 [COLOR=darkblue]Then[/COLOR] WordsA(ndxA) = vbNullString [COLOR=darkblue]Else[/COLOR] bJoin = [COLOR=darkblue]True[/COLOR] [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR] [COLOR=darkblue]Next[/COLOR] ndxA [COLOR=darkblue]If[/COLOR] bJoin [COLOR=darkblue]Then[/COLOR] URLDIF = Join(WordsA, "/") [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Function[/COLOR]
Med Col of Georgia (1) (AC) Morehouse (1) (AC) |
Med Col of Georgia (1) (AC) Meharry (1) (AC) |
Meharry |
Morehouse |
Indiana (1) (AC) Loyola-Stritch (1) (AC) U Wash-Seattle (1) (AC) UCLA/Drew Med Schl (1) (AC) |
Indiana (1) (AC) Loyola-Stritch (1) (AC) UCLA/Drew Med Schl (1) (AC) |
UCLA/Drew |
U Wash-Seattle (AC) UCLA/Drew (1) |
Here's a custom function called WORDDIF that may do what you want.
To install the custom function...
- Alt+F11 to open the VBA Editor
- From the VBA menu, select Insert\ Module
- Paste the code below in the VBA Edit window
Back in Excel, put this formula in C1
=WORDDIF(A1,B1)
Code:Function WORDDIF(rngA As Range, rngB As Range) As String Dim WordsA As Variant, WordsB As Variant Dim ndxA As Long, ndxB As Long, strTemp As String WordsA = Split(rngA.Text, " ") WordsB = Split(rngB.Text, " ") For ndxB = LBound(WordsB) To UBound(WordsB) For ndxA = LBound(WordsA) To UBound(WordsA) If StrComp(WordsA(ndxA), WordsB(ndxB), vbTextCompare) = 0 Then WordsA(ndxA) = vbNullString Exit For End If Next ndxA Next ndxB For ndxA = LBound(WordsA) To UBound(WordsA) If WordsA(ndxA) <> vbNullString Then strTemp = strTemp & WordsA(ndxA) & " " Next ndxA WORDDIF = Trim(strTemp) End Function
However, I have come to some obstacles.
1. Sometimes both the addition and deletion cell will print the same name.
2. I want to delete the numbers with parentheticals "(1)" and "AC"
3. Ultimately it would be nice to print a new column with: additions (italicized) and deletions (strike through)
[COLOR=darkblue]Function[/COLOR] WORDDIF(rngA [COLOR=darkblue]As[/COLOR] Range, rngB [COLOR=darkblue]As[/COLOR] Range) [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR]
[COLOR=green]'Remove linefeed characters and exclude parentheticals[/COLOR]
[COLOR=darkblue]Dim[/COLOR] WordsA [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Variant[/COLOR], WordsB [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Variant[/COLOR]
[COLOR=darkblue]Dim[/COLOR] ndxA [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR], ndxB [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR], strTemp As [COLOR=darkblue]String[/COLOR]
WordsA = Split(Replace(rngA.Text, vbLf, " "), " ") [COLOR=green]'remove linefeed[/COLOR]
WordsB = Split(Replace(rngB.Text, vbLf, " "), " ") [COLOR=green]'remove linefeed[/COLOR]
[COLOR=darkblue]For[/COLOR] ndxB = [COLOR=darkblue]LBound[/COLOR](WordsB) [COLOR=darkblue]To[/COLOR] [COLOR=darkblue]UBound[/COLOR](WordsB)
[COLOR=darkblue]For[/COLOR] ndxA = [COLOR=darkblue]LBound[/COLOR](WordsA) [COLOR=darkblue]To[/COLOR] [COLOR=darkblue]UBound[/COLOR](WordsA)
[COLOR=darkblue]If[/COLOR] [COLOR=darkblue]StrComp[/COLOR](WordsA(ndxA), WordsB(ndxB), vbTextCompare) = 0 [COLOR=darkblue]Then[/COLOR]
WordsA(ndxA) = vbNullString
[COLOR=darkblue]Exit[/COLOR] [COLOR=darkblue]For[/COLOR]
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
[COLOR=darkblue]Next[/COLOR] ndxA
[COLOR=darkblue]Next[/COLOR] ndxB
[COLOR=darkblue]For[/COLOR] ndxA = [COLOR=darkblue]LBound[/COLOR](WordsA) [COLOR=darkblue]To[/COLOR] [COLOR=darkblue]UBound[/COLOR](WordsA)
[COLOR=green]'Exclude parentheticals[/COLOR]
[COLOR=darkblue]If[/COLOR] [COLOR=darkblue]Not[/COLOR] WordsA(ndxA) [COLOR=darkblue]Like[/COLOR] "(*)" [COLOR=darkblue]Then[/COLOR] strTemp = strTemp & WordsA(ndxA) & " "
[COLOR=darkblue]Next[/COLOR] ndxA
WORDDIF = Application.Trim(strTemp)
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Function[/COLOR]
Med Col of Georgia (1) (AC) Morehouse (1) (AC) |
Med Col of Georgia (1) (AC) Meharry (1) (AC) |
Meharry |
Morehouse |
Med Col of Georgia, Morehouse, Meharry* |
Columbia (1) (AC) Northwestern (1) (AC) Southern Calif (1) (AC) U Chicago-Pritzker (1) (AC) U Michigan (1) (AC) Wash U St Louis (1) (AC) |
Northwestern (1) (AC) Southern Calif (1) (AC) U Chicago-Pritzker (1) (AC) U Michigan (1) (AC) |
Columbia Wash U St Louis |
Columbia Wash U St Louis |
Columbia, Wash U St Louis |
Based on your examples:
1. In the data, the difference in number of lines (linefeed characters) created words that "looked" the same but one included a hidden a linefeed. The code below strips linefeed characters.
2. Modified code excludes all parentheticals
3. That's just a column font format. It has nothing to do with the code.
Code:[COLOR=darkblue]Function[/COLOR] WORDDIF(rngA [COLOR=darkblue]As[/COLOR] Range, rngB [COLOR=darkblue]As[/COLOR] Range) [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR] [COLOR=green]'Remove linefeed characters and exclude parentheticals[/COLOR] [COLOR=darkblue]Dim[/COLOR] WordsA [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Variant[/COLOR], WordsB [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Variant[/COLOR] [COLOR=darkblue]Dim[/COLOR] ndxA [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR], ndxB [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR], strTemp As [COLOR=darkblue]String[/COLOR] WordsA = Split(Replace(rngA.Text, vbLf, " "), " ") [COLOR=green]'remove linefeed[/COLOR] WordsB = Split(Replace(rngB.Text, vbLf, " "), " ") [COLOR=green]'remove linefeed[/COLOR] [COLOR=darkblue]For[/COLOR] ndxB = [COLOR=darkblue]LBound[/COLOR](WordsB) [COLOR=darkblue]To[/COLOR] [COLOR=darkblue]UBound[/COLOR](WordsB) [COLOR=darkblue]For[/COLOR] ndxA = [COLOR=darkblue]LBound[/COLOR](WordsA) [COLOR=darkblue]To[/COLOR] [COLOR=darkblue]UBound[/COLOR](WordsA) [COLOR=darkblue]If[/COLOR] [COLOR=darkblue]StrComp[/COLOR](WordsA(ndxA), WordsB(ndxB), vbTextCompare) = 0 [COLOR=darkblue]Then[/COLOR] WordsA(ndxA) = vbNullString [COLOR=darkblue]Exit[/COLOR] [COLOR=darkblue]For[/COLOR] [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR] [COLOR=darkblue]Next[/COLOR] ndxA [COLOR=darkblue]Next[/COLOR] ndxB [COLOR=darkblue]For[/COLOR] ndxA = [COLOR=darkblue]LBound[/COLOR](WordsA) [COLOR=darkblue]To[/COLOR] [COLOR=darkblue]UBound[/COLOR](WordsA) [COLOR=green]'Exclude parentheticals[/COLOR] [COLOR=darkblue]If[/COLOR] [COLOR=darkblue]Not[/COLOR] WordsA(ndxA) [COLOR=darkblue]Like[/COLOR] "(*)" [COLOR=darkblue]Then[/COLOR] strTemp = strTemp & WordsA(ndxA) & " " [COLOR=darkblue]Next[/COLOR] ndxA WORDDIF = Application.Trim(strTemp) [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Function[/COLOR]
3. A User Defined Function (UDF) like this cannot affect the text formatting of its result. A macro could be created to compare two columns of data and output a column of formatted multi-line results, but that would go beyond the scope of this thread which is centered around the WORDDIF function. It would be best if you started a new thread for a request like that.
4. The WORDDIF function is designed to do a word-to-word comparison. Your data is more of a multi-word (school) phrase-to-phrase comparison. Based on how this function is structured, getting the school separator to go in the right place for all scenarios is more involved than just replacing the Parentheticals with a separator. It can be done, but it would be better accomplished with a whole new function (or macro as suggested in #3) that's designed to compare phases or multi-word schools specific to your data. Again, beyond the scope of this thread.
Training / Books / Sites as of 2/12/2016
I don't know if that helps, but I am looking for a way to compare two strings and see that their differences is either a space " " or a comma ",".