Remove duplicates based on multiple values

[Abgar]

Hi all,

Hoping someone could assist with a solution to a problem i have...

I have many rows of data, in which i need to remove duplicates, but based on 2 values.
The values would be column B (which is a number) and column C (which is either Yes or No).

What i want to do, is remove all duplicates of column B so that i am left with only one instance of the number, but if duplicates exist, then keep the entry where Column C = Yes (if none of them have Column C = Yes, then just keep the first entry of the duplicate)

Below is a sample of the work, with highlighted in yellow the entries that i want to keep:
(ID 1 and 7 are kept because they are the first duplicate number found where the 'Accepted' = Yes)
(ID 4 is kept because it is the first duplicate number found and NONE of them are 'Accepted' = Yes)
(ID 10 & 11 are kept because they are unique - not duplicates).

Sheet1

*	A	B	C	D
1	ID	Number	Accepted?	AddedWhen
2	1	123456789	Yes	27/03/2013 04:28:43
3	2	123456789	No	27/03/2013 04:41:27
4	3	123456789	No	27/03/2013 05:50:09
5	4	987654321	No	27/03/2013 06:35:25
6	5	987654321	No	27/03/2013 06:59:13
7	6	987654321	No	27/03/2013 07:17:21
8	7	111222333	Yes	28/03/2013 04:05:29
9	8	111222333	Yes	28/03/2013 04:13:05
10	9	111222333	No	28/03/2013 04:18:22
11	10	999888777	No	28/03/2013 04:36:34
12	11	666555444	Yes	28/03/2013 04:50:21

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:33px;"><col style="width:104px;"><col style="width:74px;"><col style="width:130px;"></colgroup><tbody>
</tbody>

Is anyone able to help me with a solution to this?

Cheers

 

[Hamburgler]

Here is a formula to identify the number of instances of both column B and C values.

[TABLE="width: 128"]
<tbody>[TR]
[TD="width: 128"]=COUNTIFS($B$2:$B$999,B2,$C$2:$C$999,C2)[/TD]
[/TR]
</tbody>[/TABLE]

If the value is "1" then it can be flagged to keep. Not sure about the rest of it though...

 

[s hal]

I added the following formula in column D (make sure you use CTRL-SHIFT-ENTER to enter the formula). "Y" means its a duplicate, "N" means it is not. You could simply filter on the Y's to delete the excess rows.

<C2,COUNTIF($A$2:$A$12,A2)><C2),AND(SMALL(IF($A$2:$A$12=A2,$C$2:$C$12),1)<C2,COUNTIF($A$2:$A$12,A2)>Hope this helps!


=IF(OR(AND(COUNTIF($A$2:$A$12,A2)>1,B2<>"Yes",SMALL(IF($A$2:$A$12=A2,$C$2:$C$12),1)< C2),AND(SMALL(IF($A$2:$A$12=A2,$C$2:$C$12),1)< C2,COUNTIF($A$2:$A$12,A2)> 1,COUNT(SEARCH(A2&"yes",$A$2:$A$12&$B$2:$B$12,1))>0)),"Y","N")

Not sure why, but the forum keeps eating part of my formula.. hopefully this works (you'll have to add the two lines together to get the right result)
</C2),AND(SMALL(IF($A$2:$A$12=A2,$C$2:$C$12),1)<C2,COUNTIF($A$2:$A$12,A2)></C2,COUNTIF($A$2:$A$12,A2)>