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

 

[Yongle]

@Peter_SSs
This is why I say I am NOT signed up - here is mine

 

[DeeExcel]

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

View attachment 3805

Hi Peter

I don't see similar to above. My account page looks like below -

 

[DeeExcel]

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

Just a small feedback for future readers --

For a reason that I am not aware of, I have to first sort the Case No. column to A-Z for the macro to give the desired results. Otherwise, I get more.

Aside from that, the macro worked beautifully.

Thanks again! I was able to continue with my work.

 

[Peter_SSs]

FYI: Further information about the dynamic array functions, including UNIQUE in this thread from page 2. See also the link in post 16 of that thread.
Seems it is a lottery for who has and has not the new functions.

 

[DanteAmor]

Just a small feedback for future readers --
For a reason that I am not aware of, I have to first sort the Case No. column to A-Z for the macro to give the desired results. Otherwise, I get more.

Aside from that, the macro worked beautifully.

Thanks again! I was able to continue with my work.

It depends on which ones you want to keep.
In the following examples the macro takes the first number in the list as a base.
I guess the 2 results are correct, but in your analysis you will have to decide which ones you want to see first, maybe sort them by date

Book1
	A	B	C	D	E
1		Case	Cross		Result
2		175	159		175
3		159	175
Sheet

Book1
	A	B	C	D	E
1		Case	Cross		Result
2		159	175		159
3		175	159
Sheet