Sort

%3CLINGO-SUB%20id%3D%22lingo-sub-2568071%22%20slang%3D%22en-US%22%3ESort%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2568071%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20all%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20getting%20frustrated.%20I%20am%20trying%20to%20sort%20rows%20of%20contiguous%20data%20Columns%20A%20through%20M%20by%20the%20cell%20data%20in%20column%20H.%20I%20highlight%20all%20the%20rows%20and%20columns.%20I%20get%20a%20dialog%20box.%20It%20asks%20me%20what%20column%20I%20want%20to%20sort%20on.%20I%20say%20column%20H.%20It%20asks%20what%20I%20want%20to%20sort%20on.%20I%20say%20cell%20value.%20It%20asks%20whether%20I%20want%20ascending%20or%20descending.%20I%20say%20descending.%20Then%20I%20click%20the%20OK%20button.%20Nothing%20happens.%20No%20sort.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20used%20the%20sort%20function%20in%20excel%20before.%20I%20have%20never%20had%20this%20kind%20of%20problem.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2568071%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2569113%22%20slang%3D%22en-US%22%3ERe%3A%20Sort%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2569113%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1108428%22%20target%3D%22_blank%22%3E%40JeffM27122%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIt%20would%20help%20if%20you%20attached%20a%20sample%20workbook%20without%20sensitive%20data%20that%20demonstrates%20the%20problem.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2570555%22%20slang%3D%22en-US%22%3ERe%3A%20Sort%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2570555%22%20slang%3D%22en-US%22%3EHi%20all%3CBR%20%2F%3E%3CBR%20%2F%3EI%20experimented%20with%20Sort%20on%20a%20smaller%20data%20base%20(a%20much%20smaller%20data%20base).%20I%20am%20running%20sort%20correctly.%20It%20must%20be%20the%20immense%20size%20of%20the%20data%20base%20I%20am%20working%20with%20that%20is%20causing%20sort%20to%20fail.%3CBR%20%2F%3E%3CBR%20%2F%3EDoes%20anyone%20have%20any%20information%20on%20the%20size%20of%20data%20base%20that%20excel's%20sort%20can%20work%20on.%20I%20have%20a%20suspicion%20that%20the%20state%20just%20took%20something%20like%20an%20SQL%20data%20base%2C%20dumped%20it%20into%20excel%2C%20and%20never%20bothered%20to%20see%20whether%20anyone%20can%20actually%20use%20the%20thing.%3C%2FLINGO-BODY%3E
Occasional Contributor

Hello all

 

I am getting frustrated. I am trying to sort rows of contiguous data Columns A through M by the cell data in column H. I highlight all the rows and columns. I get a dialog box. It asks me what column I want to sort on. I say column H. It asks what I want to sort on. I say cell value. It asks whether I want ascending or descending. I say descending. Then I click the OK button. Nothing happens. No sort. 

 

I have used the sort function in excel before. I have never had this kind of problem. 

 

Thank you. 

7 Replies

@JeffM27122 

It would help if you attached a sample workbook without sensitive data that demonstrates the problem.

@Hans Vogelaar 

 

This data base is immense. It consists of a summary of results of standardized testing on various topics in various grades of every public school school in Pennsylvania, In fact, it may be the very size of the data base that may be a problem: it has over 48,000 rows, and the number of columns is 13.

 

To bring it down to manageable size, I am trying to break it up into data bases by grade and topic. In what I shown below, I have included just a few rows. I have already tried to eliminate everything not associated with the 4th grade, (grade is indicated in column H). That explains why everything in H should be 0 or 4. My idea then is to sort on H in descending order, and copy only those rows with a 4 to a data base on 4th grade. Then I will repeat that process for the various topics. I will end up with workable data bases, one per grade and topic, that I can actually scroll through to make sure everything is working correctly

 

112011103000007302AdamsBERMUDIAN SPRINGS SDBERMUDIAN SPRINGS EL SCHEnglish Language ArtsAll Students000000
112011103000007302AdamsBERMUDIAN SPRINGS SDBERMUDIAN SPRINGS EL SCHEnglish Language ArtsAll Students412324.437.427.610.6
112011103000007302AdamsBERMUDIAN SPRINGS SDBERMUDIAN SPRINGS EL SCHEnglish Language ArtsAll Students000000
112011103000007302AdamsBERMUDIAN SPRINGS SDBERMUDIAN SPRINGS EL SCHEnglish Language ArtsHistorically Underperforming000000
112011103000007302AdamsBERMUDIAN SPRINGS SDBERMUDIAN SPRINGS EL SCHEnglish Language ArtsHistorically Underperforming46816.230.936.816.2
Hi all

I experimented with Sort on a smaller data base (a much smaller data base). I am running sort correctly. It must be the immense size of the data base I am working with that is causing sort to fail.

Does anyone have any information on the size of data base that excel's sort can work on. I have a suspicion that the state just took something like an SQL data base, dumped it into excel, and never bothered to see whether anyone can actually use the thing.

@JeffM27122 

Excel could sorts all available rows (1048576). Another story that could take time, depend on which version of Excel you are and on which hardware. Version is more critical.

Hi

It is Subscription Service Microsoft 365 Version 2106.

The machine is a Dell Inspiron 3650, 64 bit

A new idea on why it will not sort.

Right now, I have reduced the data base by replicating it using if statements to put 0 in every cell unless the entry in the column representing grade is 4, in which case, I replicate the row. That gives me a data base that appears to consist of thousands of rows of zeroes and several thousand rows of data about fourth grade test results by individual school. My idea was to sort this replicated data base on grade in descending order, and then delete all the all-zero rows, export what was left to a stand-alone 4th grade data base.

Looking at the original file, I see that there are some entries that are not numeric. Perhaps that is the problem of why sort does not seem to work; the data is no consistently numeric. I tried to fix this by setting my if-statement to be explicit about entering a 4 or a 0.
I redid what I explained in my preceding post. And then changed the format of the cells in the column to Number. It still did not sort.
I want to thank everyone who spent time pondering my problem. It is now resolved. I have no idea why the "sort" button in the ribbon did not work on this monster data base, but the "sortby" function did work.