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!

Improve this question
17
  • Try two things........use COUNTIFS() rather than COUNTIF() .........restrict the range using $A$1:$A$172000
    – Gary's Student
    May 1, 2014 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, 2014 at 14:38
  • Do you have a filter applied to the table?
    – Excellll
    May 1, 2014 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, 2014 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, 2014 at 17:19

9 Answers 9

Reset to default
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
Improve this answer
4
  • I actually want non-unique records, so duplicates. Is there a way to filter for duplicates using this method?
    – steventnorris
    May 2, 2014 at 17:30
  • @steventnorris you could use conditional formatting to identify them?
    – Raystafarian
    May 2, 2014 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.
    – steventnorris
    May 2, 2014 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, 2019 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.
Improve this answer
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?
    – steventnorris
    May 2, 2014 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.
    – Will Ediger
    May 2, 2014 at 18:10
  • Regardless, have you tried this method to see if it's causing the issue?
    – Will Ediger
    May 2, 2014 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.
    – steventnorris
    May 2, 2014 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?
    – Will Ediger
    May 2, 2014 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.

Improve this answer
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

Improve this answer
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
Improve this answer
3
  • I tried this method, and I have the same problem.
    – steventnorris
    May 1, 2014 at 16:36
  • @Raystafarian This does not solve the problem. I have the SAME problem with this method.
    – steventnorris
    May 1, 2014 at 17:15
  • @steventnorris ha I misread, apologies.
    – Raystafarian
    May 1, 2014 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.

Improve this answer
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?
    – steventnorris
    May 5, 2014 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.

Improve this answer
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.

Improve this answer
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.

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .