Show text difference between 2 cells in another cell

[Joe West]

I wonder if anyone can help.......!

I have text in Column A and text in Column B.

They summarise snagging items for a particular area of a construction project.

They are the same summary with 1 weeks difference between the 2.

Over that week 2 of the snaggiing items have been resolved and remove from the list. therefore Column A = Column B less 2 items.

I would like Excel to summarise that difference, i.e the two that have been removed from the snagging list in an adjacent cell.

All help much appreciated[/b]

 

[QuietRiot]

got some sample data of A and B

then what it looks like removed.

are they separated by a comma? etc

 

[Joe West]

comma's

Yes it may well do, here is an example


TAG, CABLE TY-WRAP, DRESS & ARRANGE CABLE PROPERLY, INCORRECT FLAT BARS ON DECK FLOOR - CABLE CONDUITS OR CABLE TRAY SUPPORTS TO BE PROIVIDED

 

[QuietRiot]

I just whipped this up,

what it does is column A will have all the items ex: boat, car, bike
then in column B you'd have car, bike
in column C it would display boat


the problem is i have it taken away all spaces in answer column C. when i was building it i was using single items like above and not sentences, right now I dont really have time to fix this but try this out until then

Sub textsum()
Dim stringg As String, checkstring As String, x As Long, lastrow As Long


'---------- get last row ------------------------------
With ActiveSheet
        lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
    End With



For x = 1 To lastrow
'----------------------- get string data ---------------
stringg = Cells(x, 1).Value ' A column = items before 2 weeks
checkstring = Cells(x, 2).Value ' B column = after 2 weeks (items still left), C= items removed
stringg = Replace(stringg, " ", "")
checkstring = Replace(checkstring, " ", "")
checkstring = checkstring + ","
stringg = stringg + ","

'--------------------- if all items removed show complete --
If stringg = checkstring Then
Cells(x, 3).Formula = "*Complete!"
GoTo nextone
Else
End If



'------------------------ get the number of commas in string --------
stringLen = Len(stringg)
        For n = 1 To stringLen
            Select Case Mid(stringg, n, 1)
                Case ","
                    ans = ans + 1
                Case Else
            End Select
        Next

'--------------------------- replace items -------------------------
For l = 1 To ans
If l = ans Then checkstring2 = Replace(checkstring2, ",", "")
checkstring2 = Left(checkstring, InStr(1, checkstring, ","))

stringg = Replace(stringg, checkstring2, "")

checkstring = Replace(checkstring, checkstring2, "")
If l = ans Then stringg = Left(stringg, Len(stringg) - 1)
Next

'--------------------------- place answer in column C
Cells(x, 3).Formula = stringg
nextone:
Next


End Sub