Excel 2007: Sort by Cell Color

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

I have Excel 2007 with a fairly new computer and 2gb of RAM. When I try to
sort a spreadsheet by cell color, the program locks up and I have to quit.
There are 46000 records in the sheet - I understand this is a big ask for the
computer but with 2gb of RAM it should be able to do it.

Does anyone have any suggestions as to how I can make this work better?

Thanks.
 
I hadn't tried sorting by color (cell fill color) before (new XL2007
feature) so I created a 46000 row by 3 column 'database' with two fill
colors in the first column. It sorted in less than 5 seconds on a lesser
machine than yours. So what's different? Massive workbook otherwise? Is
it slow to calc in general? (There is calc after a sort of calc mode is
automatic). Large number of colors?

--
Jim
| Hi,
|
| I have Excel 2007 with a fairly new computer and 2gb of RAM. When I try to
| sort a spreadsheet by cell color, the program locks up and I have to quit.
| There are 46000 records in the sheet - I understand this is a big ask for
the
| computer but with 2gb of RAM it should be able to do it.
|
| Does anyone have any suggestions as to how I can make this work better?
|
| Thanks.
 
Hi Jim,

Just the one color and only appears in one column. I used conditional
formatting to generate the background color though (there's a Show Duplicates
one). Maybe that's causing it.

I've tried to copy the column and paste it again but it doesn't keep the
cell colors. I just want it to find the duplicates, set the cell colour, then
sort by it.

Any ideas?

Thanks.
 
I think you've found a bug. I ran the numbers 1 to 46000 down a column and
then copy/pasted 1 to 10 a couple times down to have some duplicates. I
used conditional formatting to highlight the duplicates and that worked
fine. Next I selected the column and clicked the Sort button on the Data
tab. As I write it's been close to 5 minutes and the sort dialog has yet to
appear.

Since this doesn't seem to work you need an alternative. Maybe you could
remove the conditional formatting, then sort normally, which will bring the
duplicated together, and then apply the duplicates conditional formatting...

--
Jim
| Hi Jim,
|
| Just the one color and only appears in one column. I used conditional
| formatting to generate the background color though (there's a Show
Duplicates
| one). Maybe that's causing it.
|
| I've tried to copy the column and paste it again but it doesn't keep the
| cell colors. I just want it to find the duplicates, set the cell colour,
then
| sort by it.
|
| Any ideas?
|
| Thanks.
|
| "Jim Rech" wrote:
|
| > I hadn't tried sorting by color (cell fill color) before (new XL2007
| > feature) so I created a 46000 row by 3 column 'database' with two fill
| > colors in the first column. It sorted in less than 5 seconds on a
lesser
| > machine than yours. So what's different? Massive workbook otherwise?
Is
| > it slow to calc in general? (There is calc after a sort of calc mode is
| > automatic). Large number of colors?
| >
| > --
| > Jim
| > | > | Hi,
| > |
| > | I have Excel 2007 with a fairly new computer and 2gb of RAM. When I
try to
| > | sort a spreadsheet by cell color, the program locks up and I have to
quit.
| > | There are 46000 records in the sheet - I understand this is a big ask
for
| > the
| > | computer but with 2gb of RAM it should be able to do it.
| > |
| > | Does anyone have any suggestions as to how I can make this work
better?
| > |
| > | Thanks.
| >
| >
| >
 
I realize this is 5 years later that the posts here BUT I, too, have discovered the dreaded slowness in Excel 2010 sorting of conditional formatting! Amazingly, I have a set of nominally 46000 electric smart meters for which I have message counts over a period of 7 days but each column of meter IDs are not exactly the same. I want to find all of the meters that are common to message counts for all 7 days. What a complicated job. Tried numerous approaches using formulas and ended up with the exact same problem mentioned above. Here is an example of some execution times for what I would think would take in some seconds for only a single column sheet of 46000 rows representing different meters (i.e., 46000 values all in a single column). Doing the conditional formatting for duplicates works instantly. But, upon pressing the sort button and hitting the custom sort button, the computer goes off, indicates that Excel is no longer responding and took 12:45 before it would bring up the dialog box for the custom sort! That is 12 minutes, 45 seconds mind you. Upon clicking to start the sort, it took 10:42 to accomplish the sort! This is ridiculous, of course. When sorting the 46000 rows of data in one column without conditional formatting, the time can't be measured because it is so fast, well less than 1 second! It appears that Excel just can handle the searching that must do IF the cells are conditionally formatted.

The earlier posting here is the first place that we have found where this issue has been mentioned. Thanks to the original poster for their comments. Our results seem to indicate that the functions can perform but it takes an eternity.
 
Back
Top