Remove duplicates and retain one. Cross-linked cases

[DeeExcel]

Hi all

I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if the community could shed some light to my darkness

What I'm trying to do is remove the duplicates + all other cross-linked cases and retain one for counting. It doesn't matter which of the cases is counted.

A sample of the table looks as below:

Date	Case No	Cross Linked Case No
03-Jan-2019	2019-1200	2019-1205
03-Jan-2019	2019-1200	2019-1206
04-Jan-2019	2019-1205	2019-1200
04-Jan-2019	2019-1205	2019-1206
04-Jan-2019	2019-1206	2019-1200
04-Jan-2019	2019-1206	2019-1205

Multiple rows with different Case Nos are created for let's say one interaction with the customer and then cross-linked with each other to denote that its coming from one interaction. But because of this structure, a simple use of remove duplicates didn't work for me, it retains 3 unique case no. instead of counting it as one.

I don't know if there's a way to find all the cross linked case no and perhaps assign a single unique number for all the rows? or any other way to achieve the desired single count.

Thanks all!

D

 

[DanteAmor]

According to your example, you should also include the data 2019-0135964

Try the following macro, if your data is in columns B and C and starts in cell B2, the result will be in column E.

Book1
	A	B	C	D	E
1		Case	Cross		Result
2		431			431
3		175	969		175
4		175	159		161
5		159	969		964
6		159	175		173
7		161	164
8		164	161
9		964	167
10		167	964
11		969	159
12		969	175
13		173	960
14		960	173
15
Sheet

Sub Remove_duplicates()
  Dim c As Range, dic1 As Object, dic2 As Object
  Set dic1 = CreateObject("Scripting.Dictionary")
  Set dic2 = CreateObject("Scripting.Dictionary")
  For Each c In Range("B2", Range("B" & Rows.Count).End(xlUp))
    If Not dic1.exists(c.Value) Then
      If Not dic2.exists(c.Value) Then dic1(c.Value) = Empty
    End If
    dic2(c.Offset(, 1).Value) = Empty
  Next
  Range("E2").Resize(dic1.Count).Value = Application.Transpose(dic1.keys)
End Sub

HOW TO INSTALL MACROs
------------------------------------
If you are new to macros, 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. To use the macro, go back to the worksheet with your data on it and press ALT+F8, select the macro name (Remove_duplicates) from the list that appears and click the Run button. The macro will execute and perform the action(s) you asked for. If you will need to do this again in this same workbook, and 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.

 

[Peter_SSs]

Excel 365 has array function =UNIQUE

That is slightly misleading. To have the the UNIQUE function you not only require Office 365, but you also need to be registered in the 'Insiders' program and even then you need to be one of the chosen 'Insiders' that Microsoft has decided to release those new functions to as they release them only in stages to Insiders.

 

[Yongle]

That is slightly misleading. To have the the UNIQUE function you not only require Office 365, but you also need to be registered in the 'Insiders' program and even then you need to be one of the chosen 'Insiders' that Microsoft has decided to release those new functions to as they release them only in stages to Insiders.

@Peter_SSs
That is strange - I have not knowingly registered as an 'Insider' and my Account does not show as being registered (although there is an open invitation to do so)
I never receive update emails from the 'Insider' program
I think I had better investigate ?
Thanks

 

[Peter_SSs]

I have not knowingly registered as an 'Insider' and my Account does not show as being registered (although there is an open invitation to do so)

That also is strange. I have not seen any notification that those new functions shave been released to all Office 365 subscribers although that was always the eventual intention. I'll also see if I can find further information.

Microsoft's Help for the UNIQUE function still notes that it is a "beta features, and currently only available to a portion of Office Insiders "

 

[DeeExcel]

Excel 365 has array function =UNIQUE which is placed only in cell E2 in example below and Excel auto-creates entries in E3 and E4

The formula in E2
=UNIQUE(B2:B7)
Does column return the required results ?

View attachment 3802

Thanks, please see above image sent to Dante for a more detailed table. (I think I should have added more Case No. initially)

The end result I am hoping to get is just a single case no, can be either of the three (2019-1200, 2019-1205 and 2019-1206) in Column E. The reason is all three case no. are cross-linked with each other.

A bit of background is that for each question a new Case No is created. But instead of counting the questions (where the UNIQUE formula could be applied), we want to instead count the number of interaction. So rows 2-7 is just one interaction.

 

[DanteAmor]

Thanks, please see above image sent to Dante for a more detailed table. (I think I should have added more Case No. initially)

The end result I am hoping to get is just a single case no, can be either of the three (2019-1200, 2019-1205 and 2019-1206) in Column E. The reason is all three case no. are cross-linked with each other.

Could you try the macro?

Book1
	A	B	C	D	E
1		Case	Cross		Result
2		2019-1200	2019-1205		2019-1200
3		2019-1200	2019-1205
4		2019-1205	2019-1200
5		2019-1205	2019-1206
6		2019-1206	2019-1200
7		2019-1206	2019-1205
Hoja4

 

[Yongle]

@DeeExcel (ref my discussion with @Peter_SSs above)

You use Excel 365
- does your build give you access to formula =UNIQUE ?
- are you registered as an 'Insider' ?

 

[DeeExcel]

@DanteAmor the macro worked!!!

@Yongle just checked and can run =UNIQUE formula, not sure about Insider registration though..

Thanks you guys, really appreciate your help!!?

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.

 

[Peter_SSs]

not sure about Insider registration though.

Click File -> Account
and see what this section says where mine says that I am signed up as an Insider.