Sorting Huge Table with numbers and 2 text values

%3CLINGO-SUB%20id%3D%22lingo-sub-2581926%22%20slang%3D%22en-US%22%3ESorting%20Huge%20Table%20with%20numbers%20and%202%20text%20values%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2581926%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20all%2C%3C%2FP%3E%3CP%3ETrying%20to%20get%26nbsp%3B%3CSPAN%3Ehuge%20data%20table%20to%20sort%20by%20column%20smallest%20to%20largest%20for%20all%20columns%20at%20once.%20Most%20cells%20are%20numbers%20but%20two%20other%20cell%20values%20are%20NS%20or%20---%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EWould%20like%20smallest%20number%20to%20show%20at%20top%20of%20each%20column%20followed%20by%20next%20smallest%20number%20below%20that%20etc%20etc.%3C%2FP%3E%3CP%3EIf%20this%20can't%20be%20done%20just%20a%20quick%20way%20to%20find%20smallest%20number%20in%20table%20would%20be%20good.%3C%2FP%3E%3CP%3EI%20have%20tried%20sort%20function%20but%20this%20does%20not%20seem%20to%20sort%20entire%20table%20at%20once%20and%20cell%20values%20NS%20or%20---%20seem%20to%20be%20randomly%20placed.%3C%2FP%3E%3CP%3EThanks%20for%20the%20help.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EUsing%20windows%2010%3C%2FP%3E%3CP%3EOffice%20365%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2581926%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2582034%22%20slang%3D%22en-US%22%3ERe%3A%20Sorting%20Huge%20Table%20with%20numbers%20and%202%20text%20values%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2582034%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1111422%22%20target%3D%22_blank%22%3E%40DCoulthard%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhy%20not%20simply%20place%20the%20formula%20%3DMIN(B3%3AB552)%20in%20B1%20and%20then%20copy%20it%20across%2C%20it%20will%20give%20you%20the%20minimum%20value%20for%20each%20column.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

Hi all,

Trying to get huge data table to sort by column smallest to largest for all columns at once. Most cells are numbers but two other cell values are NS or ---

Would like smallest number to show at top of each column followed by next smallest number below that etc etc.

If this can't be done just a quick way to find smallest number in table would be good.

I have tried sort function but this does not seem to sort entire table at once and cell values NS or --- seem to be randomly placed.

Thanks for the help.

 

Using windows 10

Office 365

2 Replies

@DCoulthard 

Why not simply place the formula =MIN(B3:B552) in B1 and then copy it across, it will give you the minimum value for each column.

@DCoulthard 

 

How about this for overall MIN?

 

WynHopkins_0-1628154634439.png

 

Or as @Subodh_Tiwari_sktneer suggests you can copy a formula across the top to get the MIN for each column