+ Reply to Thread
Results 1 to 6 of 6

alternative to FOR EACH to apply COUNTIF formula to each cell to check for DUPLICATE

  1. #1
    Registered User
    Join Date
    09-15-2013
    Location
    Singapore
    MS-Off Ver
    Excel 2007
    Posts
    56

    alternative to FOR EACH to apply COUNTIF formula to each cell to check for DUPLICATE

    Please Login or Register  to view this content.
    Something like that. Is there an alternative method to countif on a range of cells to check for duplicate because it seems really slow even with the adjustment of manual calculation and false screenupdate.

  2. #2
    Forum Expert Vikas_Gautam's Avatar
    Join Date
    06-04-2013
    Location
    Ludhiana,Punjab, India
    MS-Off Ver
    Excel 2013
    Posts
    1,850

    Thumbs up Re: alternative to FOR EACH to apply COUNTIF formula to each cell to check for DUPLICATE

    Try this..
    It will highlight the cells, the duplicates of which has been found..
    Please Login or Register  to view this content.
    OR

    You can simply try this in conditional formatting.. by selecting the Range("G2:G30000")
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    will highlight cells in Range("G2:G30000") if duplicates are found in $B$2:$B$30000
    Regards,
    Vikas Gautam
    Excel-buzz.blogspot.com

    Excel is not a matter of Experience, its a matter of Application.

    Say Thanks, Click * Add Reputation

  3. #3
    Registered User
    Join Date
    09-15-2013
    Location
    Singapore
    MS-Off Ver
    Excel 2007
    Posts
    56

    Re: alternative to FOR EACH to apply COUNTIF formula to each cell to check for DUPLICATE

    thanks Vikas.

    I guess I could try those 2 methods but actually I'm more into looking for an alternate method to paste the countif formula to each cell like pasting all 30000 cells at one shot and running the formulas instead of looping each cell and pasting 1 by 1.

  4. #4
    Registered User
    Join Date
    09-15-2013
    Location
    Singapore
    MS-Off Ver
    Excel 2007
    Posts
    56

    Re: alternative to FOR EACH to apply COUNTIF formula to each cell to check for DUPLICATE

    thanks Vikas.

    I guess I could try those 2 methods but actually I'm more into looking for an alternate method to paste the countif formula to each cell like pasting all 30000 cells at one shot and running the formulas instead of looping each cell and pasting 1 by 1.

  5. #5
    Forum Guru
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,122

    Re: alternative to FOR EACH to apply COUNTIF formula to each cell to check for DUPLICATE

    I do not think any loop for 30,000 rows will be slow. You have not posted the entire code to see which lines are making the code to slow down, better would be if you could attach a sample.

  6. #6
    Forum Expert Vikas_Gautam's Avatar
    Join Date
    06-04-2013
    Location
    Ludhiana,Punjab, India
    MS-Off Ver
    Excel 2013
    Posts
    1,850

    Re: alternative to FOR EACH to apply COUNTIF formula to each cell to check for DUPLICATE

    Okay then try this..
    I think this will be more fast..
    Please Login or Register  to view this content.
    Purely Untested as you haven't provided any workbook..
    Last edited by Vikas_Gautam; 09-01-2014 at 09:59 PM.

  7. #7
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: alternative to FOR EACH to apply COUNTIF formula to each cell to check for DUPLICATE

    If you want to add formulas:
    Please Login or Register  to view this content.
    Remember what the dormouse said
    Feed your head

  8. #8
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,316

    Re: alternative to FOR EACH to apply COUNTIF formula to each cell to check for DUPLICATE

    How about this?
    Please Login or Register  to view this content.
    If posting code please use code tags, see here.

+ 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. [SOLVED] Check Prices and apply markup percentage Formula
    By Bash66 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-14-2014, 09:38 PM
  2. [SOLVED] apply countif formula acorss multiple worksheets
    By WeeG in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 10-31-2012, 09:57 AM
  3. How to apply countif formula when data are on different sheets
    By akash kothari in forum Excel General
    Replies: 3
    Last Post: 07-22-2012, 01:57 PM
  4. Formula/macro - Apply certain formula as per duplicate cells in column L
    By rain4u in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-10-2011, 12:28 AM
  5. How to find duplicate cell to apply the same changes?
    By jkyung.park in forum Excel General
    Replies: 0
    Last Post: 06-16-2010, 08:40 AM

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