Show text difference between 2 cells in another cell

Joe West

New Member
Joined
Aug 8, 2007
Messages
11
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]
 

Some videos you may like

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

QuietRiot

Well-known Member
Joined
May 18, 2007
Messages
1,077
got some sample data of A and B

then what it looks like removed.

are they separated by a comma? etc
 

Joe West

New Member
Joined
Aug 8, 2007
Messages
11
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

Well-known Member
Joined
May 18, 2007
Messages
1,077
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


Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,469
Messages
5,596,310
Members
414,052
Latest member
Dual Showman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top