Re: Excel 2007 data corruption using sort within a filter.

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

I cannot recreate any of this. Excel 2007 sorts only filtered
records, using either the ribbon command or the filter drop-down. In
either case, all columns are sorted and rows preserved. Excel 2003
appears to behave identically. What am I doing wrong?

1. In range A1:J1, enter ="Column "&COLUMN() then copy and paste
values.
2. In range A2:J20, enter =RANDBETWEEN(1,100) then copy and paste
values.
3. Copy range over starting at A25
4. Filter Column 1 to >=50
5. Use sort ascending from Column 1 filter dropdown
6. Unfilter data - only filter rows sort
7. Use sort ascending from Column 1 filter dropdown
8. Use sort ascending from Column 1 filter dropdown on the copied
range

Conditional formatting does not reveal any discrepancies. With Ribbon
sort, result is the same. In Excel 2003, replace step 2 with
=INT(RAND()*100)+1 and result is the same.


On Nov 11, 8:46 pm, J_Harrington
<JHarring...@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:
We're seeing an issue in Excel 2007 related to sorting data using the sort
within an auto filter. Excel behaves differently if you run the sort from
the filter drop-down or if you run it from the Ribbon. From the Ribbon, the
sort function works every time for all columns. From the drop-down, Excel
only sorts the filtered columns.

This behavior is different than 2003 where Excel sorts all of the columns
regardless of how many columns have filters. This is a problem since there
are manyspreadsheets created in earlier versions of Excel with filters on
only some columns and there is no warning that the data will become
inconsistent. Any assistance is greatly appreciated.

Below are the steps to recreate the problem.

1. Create a blank spreadsheet in Excel 2007.
2. Populate the first row with column headers.
3. Populate a few rows worth of data.
4. Select some, but not all, columns and click the Filter button on the
Ribbon. This applies filters to the columns you select, not all columns.
5. Use the sort feature in the drop-down menu in the column header to sort
the data.
6. No warning is given. Only filtered rows sort. Your table now has
inconsistent data


.

Relevant Pages

  • Excel 2007 data corruption using sort within a filter.
    ... We're seeing an issue in Excel 2007 related to sorting data using the sort ... the filter drop-down or if you run it from the Ribbon. ...
    (microsoft.public.excel.misc)
  • Re: Inserting a bunch of rows at once.
    ... can be sorted, and that only one entry per sort key would have been ... Microsoft MVP - Excel ... > the duplicate is where you want the blank row) ... > 7) Remove the filter - you should have desired result ...
    (microsoft.public.excel.misc)
  • Re: Cant get autofilter applied to every row
    ... Sort of like what I said to start with. ... Filter on a defined range name. ... > Excel likes to use blank columns and rows as "end of ranges". ... >> and autofilter will view the first row as the column names. ...
    (microsoft.public.excel)
  • RE: Auto Filter
    ... Vaya con Dios, ... Those are the sort functions that I am trying to get to work. ... I have been using the Auto Filter ... each cell is actually in that cell rather than being "carried over" from the ...
    (microsoft.public.excel.worksheet.functions)
  • Re: Need to scroll up to access the ascending/descending option in fil
    ... I am/was aware of the Sort Ascending and Descending options in the Autofilter ... default selections in a filter list as far as I am concerned. ...
    (microsoft.public.excel.worksheet.functions)