+ Reply to Thread
Results 1 to 3 of 3

Remove duplicates - CountIf / SumProduct not working

  1. #1
    Registered User
    Join Date
    07-12-2017
    Location
    Edmonton, AB
    MS-Off Ver
    2011 for MAC
    Posts
    2

    Question Remove duplicates - CountIf / SumProduct not working

    Hello everyone.

    I am working on a project that has a lot of spreadsheets each containing thousands of duplicates, which I must remove. I used Mr. Charles H. Pearson 's DeleteDuplicateRows script in order to try and delete the duplicate rows found in the spreadsheets. You can find his original code here and I will also attach below the function I created using his code.

    Now the problem: I want to run the code twice, each time for different columns: first at a column representing the code and the second represents text description. Now, you might ask yourself why I am running it twice, or even the reason why I am using Mr. Pearson's method since Excel carries its own removeDuplciate function. The answer to the first question is a little strange: It seems that the unique code that represents each of the inputs is NOT unique; therefore there could be some duplicate text description under nonduplicate unique identification code. The answer to the second question is simple: VBA's remove duplicate function doesn't seem to be working on my Version of Excel (Office for Mac 2011).
    Anyhow, back to the real problem: I can easily run Mr. Pearson's code using the first column and it will definitely work, but when I try and use it in the second column it will give me the following message: Run-time error '1004'. \n Method 'CountIf' of object 'WoorkSheetFunction' failed.

    I have been trying to find a solution and or reason to why this is happening and what I could come up with is that - and please correct me if I am wrong here - the text second column (text description) contains more than 255 characters. Now I did find a solution for such problem here at the forum: use SumProduct instead of CountIf, but it still didn't work and I got the same message.

    Does anyone know anything about what's going on?

    Please Login or Register  to view this content.
    An example of an unique indetification number and a text description:

    Document globo00020010924ducv00v8b

    Boris Tabacof, da Bracelpa, tem insistido com seus pares na Câmara de Comércio Exterior, sobre a importância de incluir a celulose nos mecanismos de financiamento aos produtos industrializados de exportação. Temos de ampliar a participação desse insumo na pauta para maximizar as vantagens comparativas brasileiras, minadas pelos financiamentos de longo prazo a juros baixos de nossos competidores.O Tesouro deu um jeitinho para conseguir cumprir a meta de superávit primário de R$ 12 bilhões em 98.Transferiu para janeiro o pagamento de 70% do salário do funcionalismo.Incertezas de fim de ano. Ontem, a direção da Ford passou o dia preparando rotas de fuga para seus executivos caso a fábrica seja tomada pelos trabalhadores, segunda-feira, em protesto contra as demissões.A partir de amanhã, a Ufesp fica congelada e as dívidas do ICMS passam a ser corrigidas pela taxa Selic.Silvia Penteado (interina) DIRETO DA FONTE. (c) 1998, AGÊNCIA ESTADO LTDA.
    Last edited by luislffm; 07-12-2017 at 02:24 PM.

  2. #2
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 365 (Windows)
    Posts
    5,516

    Re: Remove duplicates - CountIf / SumProduct not working

    Try it like this, where your codes are in A, the text is in B, and C is open and available to write formulas. I have assumed that your table has headers in row 1.

    Please Login or Register  to view this content.
    Bernie Deitrick
    Excel MVP 2000-2010

  3. #3
    Registered User
    Join Date
    07-12-2017
    Location
    Edmonton, AB
    MS-Off Ver
    2011 for MAC
    Posts
    2

    Re: Remove duplicates - CountIf / SumProduct not working

    Dear Bernie Deitrick,

    Thank you very much for your help. I did run your code and it seems to work fine with the data I am working with right now. I made some small changes to your code just to fit it to my needs since I will be running it in a bigger context.
    Please find a copy of it bellow.

    I would like to thank you once again.

    Luís F. F. Marcos

    Please Login or Register  to view this content.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 13
    Last Post: 10-22-2019, 02:14 AM
  2. [SOLVED] remove duplicates not working does not rmove all
    By cfinch100 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 09-05-2015, 08:35 AM
  3. Rank Using SUMPRODUCT and COUNTIF but still getting duplicates
    By cmertel in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-28-2014, 10:33 AM
  4. Remove Duplicates Not Working!
    By walle786 in forum Excel General
    Replies: 8
    Last Post: 03-24-2013, 12:37 PM
  5. Replies: 4
    Last Post: 06-25-2012, 10:16 AM
  6. Replies: 5
    Last Post: 02-28-2012, 02:52 PM
  7. SUMPRODUCT formula with COUNTIF not working
    By Trueman_86 in forum Excel General
    Replies: 2
    Last Post: 07-15-2010, 06:58 AM

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