Compare character to character and put results in different column

[bulletshiva]

Hello,

I have a need to compare 2 columns and put the result in the third column.

The critical part here is to compare letter to letter. so unable to find a logic to do this.

Request your help

The data is as below:

[TABLE="width: 342"]
<colgroup><col span="2"><col></colgroup><tbody>[TR]
[TD]Column1[/TD]
[TD]Column2[/TD]
[TD]Result[/TD]
[/TR]
[TR]
[TD]KCVEDPAFLSQBGXZ[/TD]
[TD]KCDPLSQXZ[/TD]
[TD]VEAFBG
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Peter_SSs]

Welcome to the MrExcel board!

unable to find a logic to do this.

1. Me either if duplicate characters can occur. For example, if
col A: KEKJK
col B: KJ
then col C could be: EKK or KEK or would you want just E?

2. Could you have this?
col A: ABCD
col B: ADB
That is, the order of characters has changed as well as one missing. If so, what is the required result and why?

3. Is the second column always less characters (or equal number) to the first column?

 

[bulletshiva]

Hello Peter,

Thanks, for question
1. there are no duplicate characters, coz each letter represents a department
2. yes, the order of characters may change and will be missing in col B
3. It should match, if not i want the characters to be listed in a new column result

As said, each character represents a dept and for a specific task. By comparing with col A i would like to know the task pending.

Hope i have answered all your questions

 

[Peter_SSs]

Hope i have answered all your questions

Yes, I think so, apart from two I forgot to ask.

1. Are you looking for a worksheet formula solution (would depend on next question) or vba?
2. What version of Excel are you using?

 

[bulletshiva]

1. macro/vba should be ok.
2. i use excel 2016

 

[bulletshiva]

forgot to mention .
1. The data in column is a standard, i don't think we need to have a column for it. KCVEDPAFLSQBGXZ is a standard and it should always compare with a column of data.

 

[Peter_SSs]

Okay, if that is Excel 2016 through Office 365 then here are 3 options to choose from. If that is the stand-alone Excel 2016 then only the first two options will be applicable.

Option 1
This macro will place the results in column C. If the col A or col B values change the macro would need to run again.

Sub FindMissing()
  Dim c As Range
  
  With CreateObject("VBScript.RegExp")
    .Global = True
    For Each c In Range("C2:C" & Range("A" & Rows.Count).End(xlUp).Row)
      .Pattern = "(.)(?=.)"
      .Pattern = .Replace(c.Offset(, -1).Value, "$1|")
      c.Value = .Replace(c.Offset(, -2).Value, "")
    Next c
  End With
End Sub

Option 2
This user-defined function. Its use is shown in column D below. Post back if you need more details about how to set up this, or Option 1.

Function Missing(Col1 As String, Col2 As String) As String
  Static RX As Object
  
  If RX Is Nothing Then
    Set RX = CreateObject("VBScript.RegExp")
    RX.Global = True
  End If
  RX.Pattern = "(.)(?=.)"
  RX.Pattern = RX.Replace(Col2, "$1|")
  Missing = RX.Replace(Col1, "")
End Function

Option 3
Worksheet formula as shown in column E below. This is an array formula so should be entered without the {} but confirmed with Ctrl+Shift+Enter, not just Enter. If confirmed correctly, Excel will insert the {}. The formula can then be copied down.

Excel Workbook
	A	B	C	D	E
1	Column1	Column2	FindMissing Macro	Missing UDF	Worksheet Formula
2	KCVEDPAFLSQBGXZ	KCDPLSQXZ	VEAFBG	VEAFBG	VEAFBG
3	KCVEDPAFLSQBGXZ	KVEDPFLSQBGXZ	CA	CA	CA
4	KCVEDPAFLSQBGXZ	QD	KCVEPAFLSBGXZ	KCVEPAFLSBGXZ	KCVEPAFLSBGXZ
5	ABCD	ADB	C	C	C
Compare characters

 

[Peter_SSs]

forgot to mention .
1. The data in column is a standard, i don't think we need to have a column for it. KCVEDPAFLSQBGXZ is a standard and it should always compare with a column of data.

I missed this post when preparing my last one. I'll look at the options again & report back.

 

[Peter_SSs]

I'll look at the options again & report back.

Assuming the data to be checked is still in column B, the revised options would be

Option 1

Sub FindMissing()
  Dim c As Range
  
  Const AllDepts As String = "KCVEDPAFLSQBGXZ"
  
  With CreateObject("VBScript.RegExp")
    .Global = True
    For Each c In Range("C2:C" & Range("B" & Rows.Count).End(xlUp).Row)
      .Pattern = "(.)(?=.)"
      .Pattern = .Replace(c.Offset(, -1).Value, "$1|")
      c.Value = .Replace(AllDepts, "")
    Next c
  End With
End Sub

Option 2

Function Missing(Col2 As String) As String
  Static RX As Object
  
  Const AllDepts As String = "KCVEDPAFLSQBGXZ"
  
  If RX Is Nothing Then
    Set RX = CreateObject("VBScript.RegExp")
    RX.Global = True
  End If
  RX.Pattern = "(.)(?=.)"
  RX.Pattern = RX.Replace(Col2, "$1|")
  Missing = RX.Replace(AllDepts, "")
End Function

Option 3
As below

Excel Workbook
	A	B	C	D	E
1		Column2	FindMissing Macro	Missing UDF	Worksheet Formula
2		KCDPLSQXZ	VEAFBG	VEAFBG	VEAFBG
3		KVEDPFLSQBGXZ	CA	CA	CA
4		QD	KCVEPAFLSBGXZ	KCVEPAFLSBGXZ	KCVEPAFLSBGXZ
Compare characters (2)

 

[Rick Rothstein]

Here is another macro that should work (same column assumption that Peter made)...

Sub FindMissing()
  Dim R As Long, X As Long, Dept As String, Data As Variant, Diff As Variant
  Data = Range("B2", Cells(Rows.Count, "B").End(xlUp))
  ReDim Diff(1 To UBound(Data), 1 To 1)
  For R = 1 To UBound(Data)
    Dept = "KCVEDPAFLSQBGXZ"
    For X = 1 To Len(Data(R, 1))
      Dept = Replace(Dept, Mid(Data(R, 1), X, 1), "")
    Next
    Diff(R, 1) = Dept
  Next
  Range("C2").Resize(UBound(Diff)) = Diff
End Sub