Hi,
I do some data reporting and the raw data reports are not very good, so it requires some additional work in excel to reach our final numbers.
I am using Excel 2010, Win 7 64 bit.
My spreadsheet is one tab, no formulas, and around 20k rows. I have one column with incident numbers in it.
I would like to use conditional formatting to highlight duplicate values for the incident number. Here is where I run into problems. What I am trying to do is highlight the duplicates, then go to my date closed column, and delete all of the incidents that have blanks for the date closed.
I cannot use the "remove duplicates" function as there will be two identical incident numbers, one has the closed date and the other doesn't, and I need to delete the one without the date only.
In theory, I should be able to highlight duplicates on my spreadsheet, set filters to only show the red shaded cells, then use a filter to only select the blanks for date closed, then manually delete the records.
The problem I am running into is that after applying conditional formatting, it takes Excel 5+ minutes for the filter window to show up after clicking on it. If you select "show cells with red background" the program will then freeze up for minutes longer, sometimes crashing, sometimes eventually applying the correct filter.
If I remove the conditional formatting, applying and removing filters is nearly instantaneous. But without the conditional formatting, I can't remove the correct records.
Any ideas what is happening? I understand that going overboard with conditional formatting could affect performance, but this is a simple spreadsheet 3mb or smaller without formulas or multiple tabs and I don't see why this would cause it to hang so much.
Thanks for reading.
Bookmarks