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]

Hi and welcome to the board!

If you need the following, you can remove duplicates with the excel functionality, select the entire range, columns A through C.
Click Data > Remove Duplicates, and then Under Columns, check columns A and B
Click OK.

Book1
	E	F	G
1	Date	Case No	Cross Linked Case No
2	03-Jan-2019	2019-1200	2019-1205
3	04-Jan-2019	2019-1205	2019-1200
4	04-Jan-2019	2019-1206	2019-1200
sheet

 

[Yongle]

Formula for count of unique items (ignoring blank cells) in range B2:B1000

=SUMPRODUCT((B2:B1000<>"")/COUNTIF(B2:B1000,B2:B1000&""))

 

[DeeExcel]

Hi Dante and Yongle

Thanks both for responding.

@Dante I also tried the same, where I am scratching my head though is how to also remove all the rows that are cross-linked with each other and just retain one them. From the results you've shared, since 2019-1200, 1205 and 1206 are all linked, we just want one of the rows and the rest to be deleted.

From the original sample table, in essence, we want to keep one row and delete the rest.

Can a formula/vba help to achieve this?

@Yongle thanks for the formula. We are working on keeping the relevant rows as mentioned to Dante above. Are there other ways that you could suggest?

 

[DanteAmor]

I also tried the same, where I am scratching my head though is how to also remove all the rows that are cross-linked with each other and just retain one them. From the results you've shared, since 2019-1200, 1205 and 1206 are all linked, we just want one of the rows and the rest to be deleted.

Maybe I don't understand what you need.
You can give an example of your data and another example with the expected result.
Use XL2BB tool

Upload an excel range:
XL2BB - Excel Range to BBCode

 

[DeeExcel]

That's handy! Thanks Dante, here goes nothing..

￿￿

 

[DeeExcel]

 

[DanteAmor]

You can explain why you remove these numbers:

2019-0135159
2019-0135164
2019-0135167
2019-0135969
2019-0135960

I don't understand your middle image.
But I guess you have the table on the left and you want the table on the right.

 

[DeeExcel]

Sorry, should have given more information.

Yes you're right, left table is the raw data. I've color-coded the related cases for easier visual. And the one on the rightmost table is the end result we want to achieve.
The one in the middle is the result I get when I apply a Remove Duplicates function which doesn't yield the results intended.

The numbers you listed above are the cases that are 'crosslinked cases' where we wanted to just capture one of them. For example -- 2019-013575, 2019-0135159 and 2019-0135969 are all related (please refer to the colors). We need to retain just one of the three.

 

[Yongle]

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 ?