Excel Speed Dismal

[Felix1980]

Hello,
I'm looking for any help on my specific issue. I work with data sets that usually have a minimum of 130,000 rows, and find that I cannot do almost anything with these files without breaking them down into workbooks of 10,000 rows most of the time. My specs :

Processor : Intel Core i7-8565U CPU @ 1.8GHz
RAM : 16GB , 15.8 usable
System : 64 bit OS Windows 10 Pro
Excel Version : 2109 (Build 14430.20342) Microsoft 365

Workbook :
142,061 rows
21 columns
No formulas
No conditional Formatting
Just raw data

I want to check if there are any duplicate IDs so :
Add conditional formatting to only column A for duplicate values
Press the button on column A for the filter and get hung up.

It sits there spinning for minutes upon minutes. When it finally does finish, the drop down filter shows on screen for a second, then disappears. No I did not press on anything, or do anything after pressing the filter button.

Everything I read suggests I should be able to work with almost 9x this data just fine. Does anyone have any advice?

 

[Candyman8019]

Are your files stored on the local machine or on a network share/sharepoint/other? If you can, try saving the file to your local machine first.

 

[Fluff]

That doesn't really surprise me, are you putting the conditional formatting on the entire column, or just the used rows?

 

[Joe4]

This sort of thing typically is much easier in a database product like Microsoft Access.

 

[Felix1980]

@Candyman8019 I have attempted both with the same result
@Fluff The whole Column. Never thought to attempt just used range. Just tried, same result.
@Joe4 I have tried using Access in the past, but end up having the same issue. Spend hours uploading data from different workbooks, wait for extended periods of time for a query to process. Of course, that said, it's always on our work's servers. Perhaps, they are sub-par?

@Fluff You're not surprised? I thought that excel is supposed to be able to handle a million rows, no problem?

 

[Joe4]

Of course, that said, it's always on our work's servers. Perhaps, they are sub-par?

Sounds like that could certainly be the case.

Note that in Access you can do some things which made speed it up, like indexing the field that you are trying to find the duplicates on.

It should be able to do that without two much trouble. If everything seems to be slow, I would lean toward saying it is environmental issues, as you suspect.

 

[Alex Blakenburg]

Are you using the same workbook each time ? Can you try a new clean workbook ?
Do have access to another computer to see if it is just your machine ?
How are you getting the data into your workbook?

 

[Fluff]

It's not just the amount of rows, it's the conditional formatting that causes the problem.
If your happy to use a macro, you could use

Sub Felix()
   Dim Ary As Variant
   Dim r As Long
  
   Ary = Range("A2", Range("A" & Rows.Count).End(xlUp)).Value2
   With CreateObject("scripting.dictionary")
      For r = 1 To UBound(Ary)
         If Not .Exists(Ary(r, 1)) Then
            .Add Ary(r, 1), r + 1
         Else
            Range("A" & r).Interior.Color = 13551615
            Range("A" & .Item(Ary(r, 1))).Interior.Color = 13551615
         End If
      Next r
   End With
   Range("A1").AutoFilter 1, RGB(255, 199, 206), xlFilterCellColor
End Sub

It takes anything from 3 to 14 secs for ~186,000 rows depending on the number of duplicates.

 

[Felix1980]

Macros are completely great! Was going to look towards that if there were no obvious fixes. Thanks!

 

[Fluff]

Glad we could help & thanks for the feedback.