Remove duplicates (including original) based on a conditional?

[scootsy]

I need to find and remove all instances of duplicate values including the original, if any of the duplicates are status 1. I can't just find and delete all status 1 entries, because any names repeated that are at status 2 would need to be deleted as well. Would it be easier to search all status 1's, take the relative reference name and delete all instances of that? or is there a simpler way?

For example:

Column A Column B
[TABLE="width: 500"]
<tbody>[TR]
[TD]Status[/TD]
[TD]Name[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]D[/TD]
[/TR]
</tbody>[/TABLE]

Should become:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Status[/TD]
[TD]Name[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]C[/TD]
[/TR]
</tbody>[/TABLE]

 

[Eric W]

Welcome to the Board!

You can do this with a couple of array formulas:

	A	B	C	D	E
Status	Name	Status	Name
A	A
B	C
B	C
C
C
D
D

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]2[/TD]

[TD="align: right"][/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]2[/TD]

[TD="align: right"][/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]2[/TD]

[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]2[/TD]

[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]1[/TD]

[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]2[/TD]

[TD="align: right"][/TD]

</tbody>

Sheet10​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D2[/TH]
[TD="align: left"]{=IFERROR(INDEX($A$2:$A$8,SMALL(IF(COUNTIFS($A$2:$A$8,1,$B$2:$B$8,$B$2:$B$8)=0,ROW($B$2:$B$8)-ROW($B$2)+1),ROWS($D$2:$D2))),"")}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E2[/TH]
[TD="align: left"]{=IFERROR(INDEX($B$2:$B$8,SMALL(IF(COUNTIFS($A$2:$A$8,1,$B$2:$B$8,$B$2:$B$8)=0,ROW($B$2:$B$8)-ROW($B$2)+1),ROWS($D$2:$D2))),"")}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]



Depending on your layout and size of data, a macro might be worth considering too.

 

[Peter_SSs]

Welcome to the MrExcel board!

Your requirement is not clear to me. If the very first status value in your sample data was 1 instead of 2, would that first row appear in the required result or not?
I ask because Eric's formula would remove that row but to me it isn't a duplicate so shouldn't be removed.

If my interpretation above is correct, then perhaps you could consider this manual method.
1. Formula in C2 is copied down.
2. Use AutoFilter to filter column C for TRUE values.
3. When filtered, delete all the rows below the header.
4. Delete column C entirely

Excel Workbook
	A	B	C
1	Status	Name	Check
2	2	A	FALSE
3	2	B	TRUE
4	1	B	TRUE
5	2	C	FALSE
6	2	C	FALSE
7	1	D	TRUE
8	2	D	TRUE
Delete Dupes

 

[Aladin Akyurek]

I think the point Peter makes is sensible...

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][td]

F​

[/td][/tr][tr][td]

1​

[/td][td]Status[/td][td]Name[/td][td][/td][td]

3​

[/td][td][/td][td][/td][/tr]
[tr][td]

2​

[/td][td]

1​

[/td][td]A[/td][td][/td][td]Idx[/td][td]Status[/td][td]Name[/td][/tr]
[tr][td]

3​

[/td][td]

2​

[/td][td]B[/td][td][/td][td]

1​

[/td][td]

1​

[/td][td]A[/td][/tr]
[tr][td]

4​

[/td][td]

1​

[/td][td]B[/td][td][/td][td]

4​

[/td][td]

2​

[/td][td]C[/td][/tr]
[tr][td]

5​

[/td][td]

2​

[/td][td]C[/td][td][/td][td]

5​

[/td][td]

2​

[/td][td]C[/td][/tr]
[tr][td]

6​

[/td][td]

2​

[/td][td]C[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

7​

[/td][td]

1​

[/td][td]D[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

8​

[/td][td]

2​

[/td][td]D[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

In D1 control+shift+enter, not just enter:

=SUM(IF(ISNA(MATCH(B2:B8,IF(COUNTIFS(B2:B8,IF(A2:A8=1,B2:B8))>1,B2:B8),0)),1))

In D3 control+shift+enter, not just enter, and copy down:

=IF(ROWS($D$3:D3)>$D$1,"",SMALL(IF(ISNA(MATCH($B$2:$B$8,IF(COUNTIFS($B$2:$B$8,IF($A$2:$A$8=1,$B$2:$B$8))>1,$B$2:$B$8),0)),ROW($A$2:$A$8)-ROW($A$2)+1),ROWS($D$3:D3)))

In E3 just enter, copy across to F3, and down:

=IF($D3="","",INDEX(A$2:A$8,$D3))