sorting multiple columns

[Guest]

Hello friends: I need some help. I know how to do sorting in general but I am
not an excel expert. The problem is as shown below:

37.66 38 38 38 38 38.25
97.2 100 92 100 78.7 100
..5 38.25 55 88.5 100 16
23.75 10 56 10 18.75 19.2
53.5 8.4 100 9.75 10.75 43.75
56.25 9 10 12.5 12.86 16

The above is a smaller version. I have almost 100 columns or more. I want to
sort each column from 'smallest to largest'. They are all independent. I do
not want to go column by column sorting each of them. Is there a way to sort
the entire thing in such a way that each column has its contents sorted from
small to large.

I appreciate all the help. Thanks in advance.

amstro.

 

[Guest]

This should do what you want:
=IF(COUNT(A:A)>=ROWS($1:1),SMALL(A:A,ROWS($1:1)),"")

Regards,
Ryan---

 

[Guest]

Thanks for the help Ryan. Can you please explain how to implement the
solution. I tried copying your formula in one of the cells (below all my
data) and it tells me there's an error in the formula and would I like to
correct it. Once I accept the default correction it gives me a circular
reference error. Please help.

Thanks again.

 

[Guest]

Ryan: I tried using the small function and it worked. gaain thanks a lot for
the help. However, I have a consequent problem now. After sorting I see that
some rows are exactly identical. I want to eliminate those rows. Is there a
function that will check rows and eliminate identical/duplicate rows.

Once again thanks a lot for your help.

 

[Guest]

Sure, this is possible. First, you may want to copy/paste special the
results of the sort before deleting dupes. It is always a good idea to test
the macro on a sample before you start deleting your actual data! Also, I
think you will encounter some issues with the function I gave you earlier and
the macro that I am giving you now. Anyway, here ya go:

Sub CheckForDupes()
'Assumes duplicates are in ColumnA
Dim RowNdx As Long
Dim ColNum As Integer
ColNum = Selection(1).Column 'set number to match the proper column
For RowNdx = Selection(Selection.Cells.Count).Row To _
Selection(1).Row + 1 Step -1
If Cells(RowNdx, ColNum).Value = Cells(RowNdx - 1, ColNum).Value Then
Cells(RowNdx, ColNum).Delete shift:=xlUp
End If
Next RowNdx
End Sub


Regards,
Ryan---

 

[Guest]

Thanks once again Ryan. Worked like magic

Sure, this is possible. First, you may want to copy/paste special the
results of the sort before deleting dupes. It is always a good idea to test
the macro on a sample before you start deleting your actual data! Also, I
think you will encounter some issues with the function I gave you earlier and
the macro that I am giving you now. Anyway, here ya go:

Sub CheckForDupes()
'Assumes duplicates are in ColumnA
Dim RowNdx As Long
Dim ColNum As Integer
ColNum = Selection(1).Column 'set number to match the proper column
For RowNdx = Selection(Selection.Cells.Count).Row To _
Selection(1).Row + 1 Step -1
If Cells(RowNdx, ColNum).Value = Cells(RowNdx - 1, ColNum).Value Then
Cells(RowNdx, ColNum).Delete shift:=xlUp
End If
Next RowNdx
End Sub


Regards,
Ryan---