Weird sort behavior

**mfactor** · 11-20-2009, 06:05 PM

Sorting is working on all but the last 100 rows of a 2100+ row spreadsheet. Why won't it sort the entire spreadsheet?

Relevant background: I recently took over this spreadsheet used as a membership database. It was spilt into two spreadsheets when an error prevented it from being expanded some time back (before my time with the spreadsheet). I'm trying to clean up the mess and recombine the spreadsheets into one.

I added columns to both spreadsheets so that the columns would line up properly and then copied all the rows from the smaller spreadsheet into the larger one. No problem.

But now, the sorting won't work on the entire spreadsheet. I've ensured that there aren't any completely blank rows or columns. And each column has a name.

If I select a smaller set of rows around the boundary area between the "old" and "new" data, it sorts those rows just fine. It's just when I select the entire spreadsheet that it doesn't sort the entire thing. [Actually, I purposefully don't select any of the spreadsheet to ensure that after picking my primary and secondary sorting fields, it should sort the entire database.]

I did not copy over the header row from the "new" spreadsheet.

Probably irrelevant background: There are formulas in a few of the larger spreadsheet columns that weren't copied into the smaller one so I've copied those into the cells from the smaller sheet as appropriate. Didn't make a difference, that I could tell.

I appreciate any help you can offer.

Thanks!

More information...

I hadn't tried to cut and paste the entire thing but when I did, that gave me some insight into what's going on - or not going on...

If I do a Ctrl-a (select all), it only selects 2027 rows and Z columns instead of 2129 rows and AE columns.

If I can solve this problem, the sort should solve itself - I think.

**somesoldiers** · 11-20-2009, 06:16 PM

attach a smaller sample maybe containing sections from the good and the bogey data?

**Richard Buttrey** · 11-20-2009, 06:20 PM

Hi,

In the column or columns that you're sorting on, what values are in the last 100 rows, and what values are in the other rows. Are you sure that the sort is not correctly sorting these values? Could there be a mixture of text which looks like numbers and numeric values?

Also can you clarify your comment that you don't select any of the spreadsheet which contradicts your other statement.

Upload the workbook so that we can take a look.

Rgds

**mfactor** · 11-20-2009, 06:44 PM

Thanks for your help. I can't post the spreadsheet due to privacy concerns but I have continued to play and have more information...

When sorting, I want all fields for every row included in the sort, therefore I'm not selecting any fields before I select Data->Sort.

Here's some new information:

If I delete the first 2000 rows and do a Ctrl-a, it only selects those rows that are left from the large database. Likewise, if I click on a single cell and select Data->Sort, it only sorts those rows left from the original large spreadsheet.

If I select a subset of rows "by hand" that includes rows from the original large spreadsheet and the smaller one I added to it, when I go to Data->Sort, instead of getting the fieldnames in the drop down list boxes for primary, secondary, etc sorting, I get Column A, Column B, etc. This is true even if I limit the "by hand" selection to those rows from the original database. However, it does sort the entire selection.

So, at this point, I think "sorting" is a red herring. The question now is why Select All (Ctrl-a) doesn't select all?

More thoughts?

Thanks again.

**mfactor** · 11-21-2009, 01:12 PM

I keep replying to my own thread as I get more information...

So, I finally figured out that there are Advanced filters being used on the spreadsheet. I noticed the exact range specified in the range is the same as what gets selected when I do a Ctrl-a (select all).

Once I figure out the purpose of those filters, I expect to be able to change them to encompass the entire spreadsheet - or turn them off when not needed.

In any case, when taking over someone else's work, some of these tools can cause unexpected behavior.

Just wanted to follow up in case others experience the same behavior.

Thanks again.