MS Excel 2010: Comparison of 2 Cells and Show Differences in 3rd Cell

[gpburdell]

I was wondering if someone has a formula for MS Excel (not in VBA since I'm not good at it) to be able to help with the following:

I'm trying to compare the "text contents" from the cells in column B to the "text contents" from the cells in column A, and place in column C the difference of WHAT IS in the cells in Column B that IS NOT in column A. Basically, I would like to show in Cell "C1", what is in Cell "B1" that IS NOT in Cell "A1".

I hope the scenarios below illustrate what I'm trying to do and the results that I'm trying to achieve.

Scenarios
Cell A1: H-017, H-030, H-059
Cell B1: H-017, H-029, H-058, H-059
Cell C1: H-029, H-058 --> desired results

Another possible scenario:
Cell A1: H-098
Cell B1: H-091, H-097
Cell C1: H-091, H-097 --> desired results

Another possible scenario:
Cell A1: "empty"
Cell B1: H-010, H-028, H-029
Cell C1: H-010, H-028, H-029 --> desired results

Another possible scenario:
Cell A1: H-010, H-028, H-029
Cell B1: "empty cell"
Cell C1: "empty cell" --> desired results

Thanks GPB

 

[BarryL]

whats the max amount of different contents in cell A1 or is there? I started working on the below but need a little more info

=IF(B1="","",IF(EXACT(B1,A1),"",IF(NOT(EXACT(B1,A1)),SUBSTITUTE(B1,LEFT(A1,SEARCH(",",A1)),""),"")))

 

[gpburdell]

Hello BarryL,

The max amount of codes (H-###) that a cell in column A has is 15.
The max amount of codes (H-###) that a cell in column B has is 25.

Does that help?

Thanks
GPB

 

[gpburdell]

Hello Gurus. I think if can figure out how to use VBA...so could anyone suggest a VBA code for the issue above? Thanks you.

 

[dssJones]

Perhaps this (untested code) might help

' WHAT IS in the cells in Column B that IS NOT in column A
' Assumptions:
' 1) the "comma space" is always used to delimit each word (Length=2)
' 2) working on Sheet1, change reference as appropriate
' 3) Data starts on row 1 and is not longer than 1000 rows
' 4) If columns A, B, and C are all empty then EOD
' 5) If Column A and B are identical then C should be blank

Sub Solution()
Const dLimiter as String = ", "
Const colNbrA as integer = 1
Const colNbrB as integer = 2
Const colNbrC as integer = 3
' Const colNbrJ as integer = 10 for example
' Const colNbrT as integer = 20 for example
Dim ptr1 as Integer
Dim ptr2 as Integer
Dim Word as String
Dim Sentence as String
Dim rowNbr as Long
ptr1 = InStr(1, sheet1.cells(1, colNbrB).value, dLimiter)
For rowNbr = 1 to 1000
Do while ptr1 > 0
if len(Word)>0 then Word = Word & dLimiter
Word = Mid(Sheet1.Cells(rowNbr, ptr2+1, ptr2-ptr1-1)
if InStr(1, sheet1.cells(rowNbr, colNbrA).value, Word) = 0 then
Sentence = Sentence & Word
end if
ptr2 = ptr1
if ptr2+2 < len(sheet1.cells(rowNbr, colNbrB).value) Then
ptr1 = InStr(ptr2+2, sheet1.cells(rowNbr, colNbrB).value, dLimiter)
Else
ptr1=0
End If
Loop

Sheet1.Cells(rowNbr, colNbrC).value = Sentence
Sentence = vbnullstring
if Sheet1.Cells(rowNbr, colNbrA).value & Sheet1.Cells(rowNbr, colNbrB).value = vbnullstring THEN EXIT FOR
Next rowNbr
End Sub

 

[dssJones]

A Do Loop is embedded in a For Next.
The For Next iterates the Data rows.
The Do Loop iterates the words in Column B, and compares them to Column A, and builds a Sentence of Words, and places the Sentence in Column C

 

[Rick Rothstein]

Hello Gurus. I think if can figure out how to use VBA...so could anyone suggest a VBA code for the issue above?

Give this UDF (user defined function) a try...

[table="width: 500"]
[tr]
	[td]Function Differences(Cell1 As Range, Cell2 As Range) As String
  Dim X As Long, Parts1() As String, Parts2() As String, Txt As String, Arr() As String
  Arr = Split(Application.Trim(Replace(Cell1.Value, ",", " ")))
  Txt = " " & Application.Trim(Replace(Cell2.Value, ",", " ")) & " "
  For X = 0 To UBound(Arr)
    Txt = Replace(Txt, " " & Trim(Arr(X)) & " ", " ")
  Next
  Differences = Replace(Trim(Txt), " ", ", ")
End Function[/td]
[/tr]
[/table]

HOW TO INSTALL UDFs
------------------------------------
If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use Differences just like it was a built-in Excel function. For example,

=Differences(A1,B1)

If you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

 

[gpburdell]

Hello
I will give those a try and let you know how it turns out.

Thank you dssJones and Rick Rothstein.

GPB

 

[gpburdell]

Hello Rick,

I watched a few online videos of how to install the UDF and the links you provided. I installed the UDF and it gives the desired results.
THANK YOU so much for your help and providing that UDF.

Thanks
GPB

 

[dssJones]

Sweet