Compare two cells, and synthesize the difference.

[cheshire]

Hi,

I am comparing some data on types of differences: additions and deletions. I've used some code from a previous thread to compare two strings and find difference; and simply switched the WORDDIF arguments accordingly.

=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"

Updated code

Code:

Function WORDDIF(rngA As Range, rngB As Range) As String

'Remove linefeed characters and exclude parentheticals

Dim WordsA As Variant, WordsB As Variant
Dim ndxA As Long, ndxB As Long, strTemp As String

WordsA = Split(Replace(rngA.Text, vbLf, " "), " ") 'remove linefeed
WordsB = Split(Replace(rngB.Text, vbLf, " "), " ") 'remove linefeed

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)
'Exclude parentheticals
If Not WordsA(ndxA) Like "(*)" Then strTemp = strTemp & WordsA(ndxA) & " "
Next ndxA

WORDDIF = Application.Trim(strTemp)

End Function

The code looks great. I have a few changes I would like to make:

3. Ultimately it would be nice to print a new column with: additions (italicized) and deletions (strike through). Instead of having a deletions or additions column (which is great to visually see), I was wondering if there's a code that synthesizes the information, so that the output is: all the school names, with deletions (strike through), additions (italicized), and repeats (no change = normal).

4. Each school is separated (for the additions/deletions column). I think they just come as a list without any separation technique. So either each has its own line within the cell or separated by a column. Perhaps a comma could replace the double parenthetical.

Examples to go with the above:

3: Synthesized information:

B6=

Med Col of Georgia (1) (AC) Morehouse (1) (AC)

<tbody>
</tbody>

D6=

Med Col of Georgia (1) (AC) Meharry (1) (AC)

<tbody>
</tbody>

Deletions
G6 =WORDDIF(D6,B6)

Meharry

<tbody>
</tbody>

Additions
H6 = =WORDDIF(B6,D6)

Morehouse

<tbody>
</tbody>

I6 = {new code or something}

Med Col of Georgia,Morehouse,Meharry*

<tbody>
</tbody>

*Note: I couldn't figure out how to strike through Meharry, so I underlined it.
Also note that there are commas separating the schools in the final list. Perhaps this could be done with replacing the set of parenthetical by commas.

4. Schools separated

B91 =

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)

<tbody>
</tbody>

D91 =

Northwestern (1) (AC) Southern Calif (1) (AC) U Chicago-Pritzker (1) (AC) U Michigan (1) (AC)

<tbody>
</tbody>

H91 ==WORDDIF(B91,D91)

Columbia Wash U St Louis

<tbody>
</tbody>

{improved version}
H91 ==WORDDIF(B91,D91)

Columbia Wash U St Louis

<tbody>
</tbody>

OR

Columbia, Wash U St Louis

<tbody>
</tbody>

Note the spaces in the first example and the comma in the second.

Cheers for all your assistance.

Also, I've done a bit of coding back in the day. While I am a bit rusty, it would be nice to learn how to code for Excel. If you have any pointers or resources, that would be helpful.