+ Reply to Thread
Results 1 to 7 of 7

Remove Duplicates issue...Not Removing as it should

  1. #1
    Registered User
    Join Date
    04-23-2012
    Location
    Davie, FL
    MS-Off Ver
    Excel 2013
    Posts
    83

    Remove Duplicates issue...Not Removing as it should

    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.

  2. #2
    Valued Forum Contributor Lemice's Avatar
    Join Date
    04-13-2013
    Location
    Somewhere.
    MS-Off Ver
    Excel 2016
    Posts
    696

    Re: Remove Duplicates issue...Not Removing as it should

    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.
    (copy pasta from Ford)
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools

    Regards,
    Lem

  3. #3
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,095

    Re: Remove Duplicates issue...Not Removing as it should

    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))
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,095

    Re: Remove Duplicates issue...Not Removing as it should

    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?

  5. #5
    Registered User
    Join Date
    04-23-2012
    Location
    Davie, FL
    MS-Off Ver
    Excel 2013
    Posts
    83

    Re: Remove Duplicates issue...Not Removing as it should

    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

  6. #6
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    13,764

    Re: Remove Duplicates issue...Not Removing as it should

    maybe with an pivot table.
    Attached Files Attached Files
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  7. #7
    Registered User
    Join Date
    04-23-2012
    Location
    Davie, FL
    MS-Off Ver
    Excel 2013
    Posts
    83

    Re: Remove Duplicates issue...Not Removing as it should

    No, that wont work either. That's ok. I'll have to do the count manually. Thank you for trying. You were great.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1