Sorting across columns

[Dan in Germany]

I have a complicated sorting task. I have data for many different soil horizons as rows and the percent mineral composition as columns.

example:

horizon - Quarz - Feldspar - Smektite - Kaolinite - Vermiculite - Chlorite
1 - 80 - 5 - 0 - 14 - 1 - 0
2 - 20 - 20 - 35 - 0 - 20 - 15
3 - 30 - 0 - 15 - 35 - 0 - 20


The sum of all minerals is always 100% and in the spreadsheet I have 40-50 Minerals. I need to sort the minerals for every horizon based on their abundance. That is, for horizon 1, I would like to have the minerals sorted as such:Quarz - Kaolinite - Feldspar - Vermiculite - Chlorite - Smektite. Of course, I could individually sort every horizon by hand, but I have a huge dataset with thousands horizons. Is there a formula I can use to sort the horizons from the largest concentration to the lowest for each horizon? Or is there a way to use a "look up" funtion to list the Mineral name in the correct sorted order with the percent abundance?

Thanks for any help or tips!

Dan

 

[edokhotnik]

I have a complicated sorting task. I have data for many different soil horizons as rows and the percent mineral composition as columns.

example:

horizon - Quarz - Feldspar - Smektite - Kaolinite - Vermiculite - Chlorite
1 - 80 - 5 - 0 - 14 - 1 - 0
2 - 20 - 20 - 35 - 0 - 20 - 15
3 - 30 - 0 - 15 - 35 - 0 - 20


The sum of all minerals is always 100% and in the spreadsheet I have 40-50 Minerals. I need to sort the minerals for every horizon based on their abundance. That is, for horizon 1, I would like to have the minerals sorted as such:Quarz - Kaolinite - Feldspar - Vermiculite - Chlorite - Smektite. Of course, I could individually sort every horizon by hand, but I have a huge dataset with thousands horizons. Is there a formula I can use to sort the horizons from the largest concentration to the lowest for each horizon? Or is there a way to use a "look up" funtion to list the Mineral name in the correct sorted order with the percent abundance?

Thanks for any help or tips!

Dan

Go to Data, Sort, Options and select "Sort Left To Right".

HTH, Ed

 

[pcc]

adapt this to suit

Sub Macro1()

r = 2
Do Until IsEmpty(Cells(r, 1))
    Range(Cells(r, 1), Cells(r, 7)).Select
    Selection.Sort Key1:=Range("A" & r), Order1:=xlDescending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlLeftToRight
    r = r + 1
    Loop
End Sub

 

[Haldoug]

pcc - That marcro worked great, but if I can speak for Dan in Germany, I think he needs to sort the headings with the numbers. Is there a way to keep the mineral types in row 1 with the percentatages?

 

[Dan in Germany]

Haldoug hit the nail on the head, I do need to keep the headings intact the percentages, this is fine if I just have one horizon and then sort "Left to Right" like Edokhotlink said. Whats irritating, is that every horizon will have a different mineral order. To sort left to right I would need to have separate headings for every Horizon and then sort every horizon individually.

I had thought about a nested formula in a separate table. A lookup function would locate the percentages on horizon 1. Next the formula would look up in the top row for the names of minerals and put the name and percent of the mineral in the cell as a result. Then I could sort this row left to right. I've attemted such a formula, but with no avail. I'm I approaching this problem in the right manner?

 

[pcc]

see if you can adapt this
start with
horizon Quarz Feldspar Smektite Kaolinite Vermiculite Chlorite
1 9.3 0.6 8 3 8.4 5.2
2 2.2 9.5 8.3 5.2 2.1 0.6
3 0.6 5.9 3.5 4.3 4.2 5.1
4 8.4 8.4 8.4 1.9 6.1 8.2
5 2 2.9 2.1 3.4 2.3 7.8
6 8 9.8 6.3 0.2 7.2 5.5
7 8 0.1 6.4 5 1.3 8.1
8 3.6 7.5 3.3 5.3 6.3 6.3
9 6.4 7.1 6.4 6.5 7.2 9.9
10 0.4 4.5 3.1 7.3 0.9 7.2


run

Option Compare Text
Sub Macro1()
'
' Macro1 Macro
' Macro recorded 23/02/2011 by u59155z
'

'
sname = ActiveSheet.Name
Application.DisplayAlerts = False
For Each sht In Worksheets
If sht.Name = "_output_" Then Sheets(sht.Name).Delete
Next sht
Application.DisplayAlerts = True
Sheets.Add.Name = "_output_"
outrow = 1



Sheets(sname).Activate
r = 2
Do Until IsEmpty(Cells(r, 1))
Range(Cells(1, 1), Cells(1, 7)).Copy Destination:=Sheets("_output_").Cells(outrow, 1)
Range(Cells(r, 1), Cells(r, 7)).Copy Destination:=Sheets("_output_").Cells(outrow + 1, 1)
Sheets("_output_").Activate

    Range(Cells(outrow, 2), Cells(outrow + 1, 7)).Select
  
    Selection.Sort Key1:=Range("B" & outrow + 1), Order1:=xlDescending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlLeftToRight
       
   For c = 2 To 7
   Cells(outrow + 1, c) = Cells(outrow, c) & ": " & Cells(outrow + 1, c)
   Next c
   
    outrow = outrow + 2
    Sheets(sname).Activate
    r = r + 1
    Loop
    Sheets("_output_").Activate
lr = [a1].CurrentRegion.Rows.Count
For r = lr To 2 Step -1
If Cells(r, 1) Like "*horizon*" Then Rows(r).Delete
Next r
  
End Sub

end with

horizon Quarz Vermiculite Smektite Chlorite Kaolinite Feldspar
1 Quarz : 9.3 Vermiculite : 8.4 Smektite : 8 Chlorite: 5.2 Kaolinite : 3 Feldspar : 0.6
2 Feldspar : 9.5 Smektite : 8.3 Kaolinite : 5.2 Quarz : 2.2 Vermiculite : 2.1 Chlorite: 0.6
3 Feldspar : 5.9 Chlorite: 5.1 Kaolinite : 4.3 Vermiculite : 4.2 Smektite : 3.5 Quarz : 0.6
4 Quarz : 8.4 Feldspar : 8.4 Smektite : 8.4 Chlorite: 8.2 Vermiculite : 6.1 Kaolinite : 1.9
5 Chlorite: 7.8 Kaolinite : 3.4 Feldspar : 2.9 Vermiculite : 2.3 Smektite : 2.1 Quarz : 2
6 Feldspar : 9.8 Quarz : 8 Vermiculite : 7.2 Smektite : 6.3 Chlorite: 5.5 Kaolinite : 0.2
7 Chlorite: 8.1 Quarz : 8 Smektite : 6.4 Kaolinite : 5 Vermiculite : 1.3 Feldspar : 0.1
8 Feldspar : 7.5 Vermiculite : 6.3 Chlorite: 6.3 Kaolinite : 5.3 Quarz : 3.6 Smektite : 3.3
9 Chlorite: 9.9 Vermiculite : 7.2 Feldspar : 7.1 Kaolinite : 6.5 Quarz : 6.4 Smektite : 6.4
10 Kaolinite : 7.3 Chlorite: 7.2 Feldspar : 4.5 Smektite : 3.1 Vermiculite : 0.9 Quarz : 0.4
11 Feldspar : 9.5 Smektite : 8.5 Vermiculite : 8.4 Quarz : 6.5 Kaolinite : 5.9 Chlorite: 5.2
12 Feldspar : 9.8 Vermiculite : 8.4 Kaolinite : 8.1 Chlorite: 7.4 Smektite : 6.3 Quarz : 4.4

 

[Dan in Germany]

Thanks pcc , the result of the macro you posted looks like what I need! Unfortunately I've only run one macro my entire life. I've put off learning but now it looks like I'll have to start.
Thanks for the reply!

P.S. is there a wayto have the results come out so that the percent is infront of the mineral?

 

[pcc]

just change one line:

Option Compare Text
Sub Macro1()
'
' Macro1 Macro
' Macro recorded 23/02/2011 by u59155z
'

'
sname = ActiveSheet.Name
Application.DisplayAlerts = False
For Each sht In Worksheets
If sht.Name = "_output_" Then Sheets(sht.Name).Delete
Next sht
Application.DisplayAlerts = True
Sheets.Add.Name = "_output_"
outrow = 1



Sheets(sname).Activate
r = 2
Do Until IsEmpty(Cells(r, 1))
Range(Cells(1, 1), Cells(1, 7)).Copy Destination:=Sheets("_output_").Cells(outrow, 1)
Range(Cells(r, 1), Cells(r, 7)).Copy Destination:=Sheets("_output_").Cells(outrow + 1, 1)
Sheets("_output_").Activate

    Range(Cells(outrow, 2), Cells(outrow + 1, 7)).Select
  
    Selection.Sort Key1:=Range("B" & outrow + 1), Order1:=xlDescending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlLeftToRight
       
   For c = 2 To 7
   Cells(outrow + 1, c) = Cells(outrow + 1, c) & ": " & Cells(outrow, c)  ' this one
   Next c
   
    outrow = outrow + 2
    Sheets(sname).Activate
    r = r + 1
    Loop
    Sheets("_output_").Activate
lr = [a1].CurrentRegion.Rows.Count
For r = lr To 2 Step -1
If Cells(r, 1) Like "*horizon*" Then Rows(r).Delete
Next r
  
End Sub

 

[Dan in Germany]

Thanks again for the help with the macro, I got it to work! Now I'm having fun applying it to my whole dataset ; )

Thanks!

 

[pcc]

Glad it works for you. Good luck