Remove duplicates and retain one. Cross-linked cases

DeeExcel

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

Some videos you may like

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,205
Office Version
2007
Platform
Windows
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
 

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,361
Office Version
365
Platform
Windows
Formula for count of unique items (ignoring blank cells) in range B2:B1000

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

DeeExcel

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

Well-known Member
Joined
Dec 3, 2018
Messages
12,205
Office Version
2007
Platform
Windows
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

New Member
Joined
Jan 6, 2020
Messages
9
Office Version
365
Platform
Windows
That's handy! Thanks Dante, here goes nothing..

￿￿
 

DeeExcel

New Member
Joined
Jan 6, 2020
Messages
9
Office Version
365
Platform
Windows
1578884324617.png
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,205
Office Version
2007
Platform
Windows
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

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

Well-known Member
Joined
Mar 11, 2015
Messages
6,361
Office Version
365
Platform
Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,102,599
Messages
5,487,782
Members
407,610
Latest member
bellakim00

This Week's Hot Topics

  • Timer in VBA - Stop, Start, Pause and Reset
    [CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
  • how to updates multiple rows in muliselect listbox
    Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
  • Delete Row from Table
    I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
  • Assigning to a variable
    I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
  • Way to verify information
    Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
  • Active Cell Address – Inactive Sheet
    How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...
Top