Remove Duplicates issue...Not Removing as it should

**trosasco** · 05-17-2013, 05:19 PM

I need to change the formulas in the gray columns so that it will only count the client as one. I have to change this info every week for a report. The clients sometimes have multiple statuses so they show up duplicated. Book1.xlsxI tried to do a remove duplicates but its not working the way I want it to. Please see attached.

**Lemice** · 05-17-2013, 06:23 PM

Hello,

This formula might be what you are looking for
=--(COUNTIF($E$3:$E3,$E3)=1)

Paste it on K3 and drag it down to K24.

**FDibbins** · 05-17-2013, 06:24 PM

Im not sure why the F/R isnt working, I will look into it. In the mean time, add this to the start of your formulas, and copy down...
=IF(COUNTIF($C$3:$C3,C3)>1,"",.............

=IF(COUNTIF($C$3:$C3,C3)>1,"",IF(OR(D:D="Cancelled",D:D="Retention - Cancelled",D:D="NSF",D:D="Retention"),1,0))

**FDibbins** · 05-17-2013, 06:29 PM

OK when I tested before, I was looking at CLIENT, but removing on ASIGNEE

change my formula above to...
=IF(COUNTIF($E$2:E2,E2)>1,........

When I used "rem Dups" on your data, it wored fine? What isnt working right when you do it?

**trosasco** · 05-23-2013, 02:33 PM

When I try to remove the duplicates, it removes everyone except for 3 clients and its supposed to give me 17 clients total (If I count them manually).

Right now, The cancelled column is giving me a total of 38 cancelled (white box) which is where I should have 17. Is the problem because I have the "Pipeline Status" filtered? Please see attached..

Book2.xlsx