+ Reply to Thread
Results 1 to 4 of 4

Need to export duplicate values, excel keeps crashing!

  1. #1
    Registered User
    Join Date
    01-30-2017
    Location
    London, England
    MS-Off Ver
    2010
    Posts
    1

    Angry Need to export duplicate values, excel keeps crashing!

    Hey,

    I am working on an excel spreadsheet that contains over 200.000 rows (so a huge workbook).

    I am trying to highlight and extract instances where the same event has been recorded more than once, but excel keeps crashing when i try. I have used conditional formatting, which results in the whole sheet moving extremely slow. When I try to filter by colour while using conditional formatting, excel keeps crashing! I can't even get the filter dropdown menu to appear. On the few instances it has appeared, it crashes when I select filter by colour.

    I then tried the countif()>1 method, which captures rows that are not duplicate values, and leaves out entries that are duplicates. Is there a way to extract my duplicate values from a sheet this big, other than the two routes explained above? My laptop runs on an intel Core i5 vPro processor, and has 4 gb ram. I don't know what to do, please help!!

  2. #2
    Forum Expert tim201110's Avatar
    Join Date
    10-23-2011
    Location
    Russia
    MS-Off Ver
    2016, 2019
    Posts
    2,359

    Re: Need to export duplicate values, excel keeps crashing!

    VBA macro can do it
    a good example of your SS would be nice

  3. #3
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    13,748

    Re: Need to export duplicate values, excel keeps crashing!

    The advanced filter has a "unique records only" option. Excel 2007 has a "remove duplicates" utility on the Data tab (I assume it is still available in 2010) that will remove duplicate entries from a list. (Discussed in this tutorial: http://www.contextures.com/xladvfilter01.html see "filter unique records" and "remove duplicates" sections). These utilities would eliminate the need for the COUNTIF() function and/or the conditional formatting, which may allow the operation to occur faster. Have you tried either of these two?
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  4. #4
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,725

    Re: Need to export duplicate values, excel keeps crashing!

    Conditional Formatting is resource intensive. I'd only use it at final stage of a report in very defined area (Ex. in final dashboard as KPI indicator).

    Since you have Excel 2010, try PowerQuery add-in. You can remove duplicates in one shot and same process will be replicated on table when you refresh resulting table.

    Another method is to use MS Query and use "SELECT DISTINCT" to remove duplicates.

+ 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: 2
    Last Post: 09-21-2015, 10:49 AM
  2. Excel crashing when trying to view code before macro run; not crashing after first run
    By goonerforlyf in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-21-2015, 05:27 PM
  3. Find duplicate field values and export those records to excel file
    By sirhacksalot in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-12-2013, 10:59 AM
  4. Excel crashing when Pasting Formulas as Values
    By XOR LX in forum Excel General
    Replies: 1
    Last Post: 11-08-2013, 11:48 AM
  5. Export Unique Values from two excel files into a third file
    By pwilkes in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-12-2013, 06:14 PM
  6. [SOLVED] Excel 2008 : Excel 2010 Export or copy values from chart
    By kwrom in forum Excel General
    Replies: 2
    Last Post: 03-31-2012, 11:35 AM
  7. Replies: 2
    Last Post: 04-28-2006, 02:00 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