Removing duplicate and original entries from a string within a single cell

[Pricey627]

Hi all,

I'm hoping someone can help me! I'm working with some data where each cell contains multiple entries (which are unique references). Essentially I want to compare A2 and B2 to show how many entries should still be live. I can get a numerical view of this in C2 by using =(LEN(TRIM(A2-LEN(SUBSTITUTE(TRIM(A2",",""))+1)-(LEN(TRIM(B2-LEN(SUBSTITUTE(TRIM(B2),",",""))+1) but what I'd like to do is show which references remain to save the end users having to go through the detail manually.

I've found multiple ways of removing duplicates (after concatenating A2 & B2) but can't work out how to remove both the duplicate entries and the originals. What I'd like to see as my end result in D2 would be 12347, 12348, 12349, 12350.

The original values are separated by a comma and space but the space could be removed if necessary (I was playing around with a TRIM & SUBSTITUTE nesting for this).

[TABLE="width: 312"]
<colgroup><col width="258" style="width: 194pt; mso-width-source: userset; mso-width-alt: 9435;"> <col width="84" style="width: 63pt; mso-width-source: userset; mso-width-alt: 3072;"> <col width="73" style="width: 55pt; mso-width-source: userset; mso-width-alt: 2669;"> <tbody>[TR]
[TD="class: xl63, width: 258, bgcolor: transparent"][TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Logged[/TD]
[TD]Closed[/TD]
[TD]Remaining[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]12345, 12346, 12347, 12348, 12349, 12350[/TD]
[TD]12345, 12346[/TD]
[TD][TABLE="width: 73"]
<tbody>[TR]
[TD]4[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]

 

[Peter_SSs]

Welcome to the MrExcel board!

One way would be to use a user-defined function as below. To implement ..
1. Right click the sheet name tab and choose "View Code".
2. In the Visual Basic window use the menu to Insert|Module
3. Copy and Paste the code below into the main right hand pane that opens at step 2.
4. Close the Visual Basic window.
5. Enter the formula as shown in the screen shot below and copy down.
6. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm)

Function Remove(sLogged As String, sClosed 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 = "(" & Replace(Replace(sClosed, " ", ""), ",", "|") & ")(, )?"
  Remove = Replace(Application.Trim(RX.Replace(Replace(sLogged, ", ", " "), "")), " ", ", ")
End Function

Note that I have also suggested a different formula for column C. I couldn't evaluate your formula as what you posted isn't a valid formula.

Excel Workbook
	A	B	C	D
2	12345, 12346, 12347, 12348, 12349, 12350	12345, 12346	4	12347, 12348, 12349, 12350
3	26598	26598	0
4	56983, 65432	15623, 98654	2	56983, 65432
5	12345, 98765	98765	1	12345
6			0
7	85698, 56984, 75355	56984, 85698, 75355	0
8	12345, 98765	12345	1	98765
Sheet1

 

[Pricey627]

Thanks Peter, that's exactly what I was looking for!

 

[Peter_SSs]

Thanks Peter, that's exactly what I was looking for!

You're welcome. Glad it worked for you.