Remove duplicates and retain one. Cross-linked cases

DeeExcel

New Member
Joined
Jan 6, 2020
Messages
9
Office Version
  1. 365
Platform
  1. Windows
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:

DateCase NoCross Linked Case No
03-Jan-20192019-12002019-1205
03-Jan-20192019-12002019-1206
04-Jan-20192019-12052019-1200
04-Jan-20192019-12052019-1206
04-Jan-20192019-12062019-1200
04-Jan-20192019-12062019-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
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
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
EFG
1DateCase NoCross Linked Case No
203-Jan-20192019-12002019-1205
304-Jan-20192019-12052019-1200
404-Jan-20192019-12062019-1200
sheet
 
Upvote 0
Formula for count of unique items (ignoring blank cells) in range B2:B1000

=SUMPRODUCT((B2:B1000<>"")/COUNTIF(B2:B1000,B2:B1000&""))
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0
1578884324617.png
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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 ?

Unique.jpg
 
Upvote 0

Forum statistics

Threads
1,212,933
Messages
6,110,751
Members
448,295
Latest member
Uzair Tahir Khan

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top