0

I'm having an odd issue in excel that I haven't run into before. I have an excel sheet that I'm attempting to pull some specific records from, requiring that I know of duplicates. I am using a COUNTIF to filter to my duplicates then pulling based on other filters; however, the COUNTIF seems to be hanging up excel. I've outlined my process below.

  1. Create column with =COUNTIF(A:A,A2)
  2. Copy formula down the column
  3. Save worksheet (Hangs/takes ages to finish) or Copy column and paste values (Hangs/takes ages to finish

I have done this in Excel 2010 and 2013 with the same results on two different PCs. The worksheet only has 172000 lines in it. The formula executes fine to get the values, but hangs when I try to save or to copy and paste the column values. Without that column, the worksheet behaves normally. I've never encountered this before.

Thanks in advance!

17
  • Try two things........use COUNTIFS() rather than COUNTIF() .........restrict the range using $A$1:$A$172000 May 1 '14 at 14:30
  • @Gary'sStudent Same issue with both of those. If I wait for ages and ages for it to finally save, it seems to behave normally afterwards, or did once I got it to save one time.
    – steventnorris
    May 1 '14 at 14:38
  • Do you have a filter applied to the table?
    – Excellll
    May 1 '14 at 16:14
  • You could apply conditional formatting so that the duplicates have a different fill color then use "Filter by color."
    – PFitz
    May 1 '14 at 16:24
  • 1
    @steventnorris Scroll down to row 172k and take a look the scrollbar slider on the right. Is it at the bottom of the bar? If not, highlight row 172001, hold CTRL + shift and press the down arrow to highlight all the rows below. Right click on a row number on the left, delete the rows, then save the workbook to reset the scrollbar. This may cure your slowdowns. Keep in mind that it won't take effect until you save the workbook.
    – PFitz
    May 1 '14 at 17:19
2

Why not just apply a filter instead of using a countif()?

  1. Select your data, go to Data - Sort & Filter - Advanced to place an advanced filter.
  2. Filter the list, in-place
  3. Select the entire table as the List range
  4. Select column A as the Criteria range
  5. Check the box next to Unique records only
4
  • I actually want non-unique records, so duplicates. Is there a way to filter for duplicates using this method? May 2 '14 at 17:30
  • @steventnorris you could use conditional formatting to identify them? May 2 '14 at 18:53
  • That was suggested above, and conditional formatting works fine, but when I try to filter by the color, the same issues occur. May 2 '14 at 18:55
  • Since the target is.. "requiring that I know of duplicates".. @steventnorris may use this method, to get non-unique cells.. || Method : (after @.Raystafarian step 5).. add one an extra columm, and type '1' in it.. , then put filter on that extra column.. Filter for non-'1' cells.. done... that's = your non-unique cells. /(^_^) [p/s : I don't intent to overwrite the answer post and add this as step 6.. I'll rather let the answer owner do it.]
    – p._phidot_
    May 21 '19 at 4:34
2

Try the following:

  1. Turn off automatic calculations. (Formulas ribbon tab, Calculation group, Calculation Options drop down, Manual)
  2. Turn off "Recalculate workbook before saving" (Options, Formulas tab, Calculation Options section)
  3. Change the reference to column A to a reference to a dynamic named range.
19
  • 1
    I'm a bit against this method as an end user who I'm not confident will remember to manually refresh to sheet will be using this in the end. Surely a CountIf should function on the sheet without hanging, right? Somehow? May 2 '14 at 17:31
  • As a user, you will likely notice that most users who deal with huge workbooks with large amounts of calculations would go insane if they didn't have Manual Calculations turned on. Your productivity increases because otherwise, you have to wait for calculation times as you're working. When you need data refreshed, you often just refresh the worksheet (or even range) that you're interested in. It's a necessary evil, and you quickly get used to it. May 2 '14 at 18:10
  • Regardless, have you tried this method to see if it's causing the issue? May 2 '14 at 18:15
  • This resolves the issues when saving and filtering, but when calculating, I still have a "Calculating: (4 proccessor(s))" message at the bottom that takes ages to complete. Using "Calculate Now", I can at least click about and it stops the calculations, but that's not really better. May 2 '14 at 18:40
  • Have you tried to change the range reference to a dynamic named range, or to just test if it helps, a regular named range? May 2 '14 at 19:20
1
  1. Pivot table with the column you are trying to find duplicates in.

  2. Count the number of times your value shows up.

  3. Copy anything with count > 1 onto another sheet

  4. Use this sheet as a VLOOKUP table, to flag duplicates on your original sheet.

  5. Copy/Paste values from your VLOOKUP (because the formula is volatile, and you don't want Excel recalculating every time a change is made).

  6. Filter for duplicates.

The Vlookup operation still takes a bit of time, but it is significantly faster than COUNTIF.

1

I found my countif formula =COUNTIF($E$2:$E$111111,E2) running extremely slow so have done the following,

Pivot your data

Put in row the ID you want to count Put in values the count of the ID you want to count

Go back to your data insert a column and create an index and match which matches the id column in your data to the column of your pivot with the id in the rows.

Then index back the count of the id column on your pivot original would be: =COUNTIF($E$2:$E$111111,E2)

using index and match

=INDEX(Pivot!B:B,MATCH(Data!E:E,Pivot!A:A,0))

This sped up the calculations massively

Thanks

0

Try using an array formula, it should perform significantly better.

  1. If your formula is column b, then: select B2:B172000
  2. Type =COUNTIF($A$1:$A$172000,A1:A172000)
  3. Press Cntrl-Shift-Enter
3
  • I tried this method, and I have the same problem. May 1 '14 at 16:36
  • @Raystafarian This does not solve the problem. I have the SAME problem with this method. May 1 '14 at 17:15
  • @steventnorris ha I misread, apologies. May 1 '14 at 17:19
0

You could go about this in a different way to solve your problem - Use a Pivot Table on Column A. I'm assuming you have a header in A1 and the data extends from A2 to some variable row beyond A2.

  1. Highlight column A. Click Insert, Pivot Table.
  2. Select the destination location of your Pivot Table, then click OK.
  3. In the "PivotTable Fields" dialog box (not sure exactly what this looks like in 2010; I'm using 2013), drag whatever your header is for column A down into the "Rows" box and the "Values" box.
  4. In my instance of Excel, the Values for the header defaults to count. If it doesn't for you, left click the line in the header box "sum/count/average/??... of Header", and then click "Value Field Settings". Then under "Summarize value field by", you select Count.

You could turn on Automatic calculation. PivotTables have to be refreshed explicitly. Try it out and see if it works for you.

1
  • My could isn't to get a count of these rows, it is to copy these rows out into another sheet to be used separately. I know pivots can do count, sum, etc., but I haven't had any luck in getting the actual rows. Is that possible? May 5 '14 at 12:30
0

Another option you might look into is copying the data (values only) to a new workbook and see if the performance issue still exists. It might be that the workbook is corrupted somehow.

0

I bumped into this very same problem and googled for solution into this thread. My formula in column B was following: =COUNTIF($A1$:A,A2)

so that the count for each new row copied will always start from the beginning of the dataset.

The countif takes some time (in my sheet +100k rows), but it did the job. My Excel crashes if I want to Copy as values, and I have a fast computer w 16gig RAM.

Then I just discovered the simplest solution! Add a column next to the formula column (e.g. Column C), and make a simple formula referencing the column B, like =B1*1 or =VALUE(B1) and copy that all the way down. After that, you just select this simple formula column, and Copy as values.

After the values in Column C are copied, you can delete the original heavy formula column OR create a new Excel sheet with only values pasted to it.

0

I have been experiencing the same issue with a CountIf formula in a table with several thousands of rows. By following these steps, I was able to cut the times to less than half (from ~96 seconds to less than 40 seconds):

  1. Created an additional column with sequential numbering (just to make sorting back possible).
  2. Sorted the column used in the CountIf as necessary (ascending or descending)
  3. Calculated the CountIf
  4. Converted the CountIf results from formulas to values.
  5. Sorted the data back using the sequential numbering column (mentioned on step 1).

In my case, I was using the CountIf to obtain the row number of a value in the list (like SQL's row_number()). The list I used to perform the steps had duplicated values.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.