Bringing back column headings for descending values

[kimhesk]

Hi, i hope someone can help me as this is driving me potty.

I have student grades listed like below

[TABLE="width: 325"]
<!--StartFragment--> <colgroup><col width="65" span="5" style="width:65pt"> </colgroup><tbody>[TR]
[TD="width: 65, align: center"][/TD]
[TD="class: xl63, width: 65, align: center"]Biology[/TD]
[TD="class: xl63, width: 65, align: center"]Chemistry[/TD]
[TD="class: xl63, width: 65, align: center"]History[/TD]
[TD="class: xl63, width: 65, align: center"]Physics[/TD]
[/TR]
[TR]
[TD="class: xl63, align: center"]Student 1[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"]2[/TD]
[/TR]
[TR]
[TD="class: xl63, align: center"]Student 2[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]5[/TD]
[/TR]
[TR]
[TD="class: xl63, align: center"]Student 3[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]3[/TD]
[/TR]

<!--EndFragment-->​

</tbody>[/TABLE]


and I want to bring back the subject names in descending order, like below:

[TABLE="width: 541"]
<colgroup><col span="5"><col span="4"></colgroup><tbody>[TR]
[TD][/TD]
[TD="align: center"]Biology[/TD]
[TD="align: center"]Chemistry[/TD]
[TD="align: center"]History[/TD]
[TD="align: center"]Physics[/TD]
[TD="align: center"]1st[/TD]
[TD="align: center"]2nd[/TD]
[TD="align: center"]3rd[/TD]
[TD="align: center"]4th[/TD]
[/TR]
[TR]
[TD="align: center"]Student 1[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]Chemistry[/TD]
[TD="align: center"]History[/TD]
[TD="align: center"]Biology[/TD]
[TD="align: center"]Physics[/TD]
[/TR]
[TR]
[TD="align: center"]Student 2[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]History[/TD]
[TD="align: center"]Physics[/TD]
[TD="align: center"]Chemistry[/TD]
[TD="align: center"]Biology[/TD]
[/TR]
[TR]
[TD="align: center"]Student 3[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]Physics[/TD]
[TD="align: center"]History[/TD]
[TD="align: center"]Biology[/TD]
[TD="align: center"]Chemistry[/TD]
[/TR]
</tbody>[/TABLE]


I can do it in the case of student 3 by using a mix of index, match and large functions but I cannot get it to work properly where i have duplicate grades.

If you have a code that works could you also briefly explain what each bit does as i have a much larger sample i need to apply it to,

Thanks

 

[kimhesk]

Does anyone have any ideas?
I tried combining the numbers and text e.g. 3 Biology 5 Physics 5 History and then trying to sort in descending order but it doesn't recognise the numbers when I sort, only the text, very frustrating!

 

[Marcelo Branco]

Try


[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]

A​

[/td][td="bgcolor: #DCE6F1"]

B​

[/td][td="bgcolor: #DCE6F1"]

C​

[/td][td="bgcolor: #DCE6F1"]

D​

[/td][td="bgcolor: #DCE6F1"]

E​

[/td][td="bgcolor: #DCE6F1"]

F​

[/td][td="bgcolor: #DCE6F1"]

G​

[/td][td="bgcolor: #DCE6F1"]

H​

[/td][td="bgcolor: #DCE6F1"]

I​

[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]

1​

[/td][td][/td][td]

Biology​

[/td][td]

Chemistry​

[/td][td]

History​

[/td][td]

Physics​

[/td][td]

1st​

[/td][td]

2nd​

[/td][td]

3rd​

[/td][td]

4th​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

2​

[/td][td]

Student 1​

[/td][td]

6​

[/td][td]

7​

[/td][td]

7​

[/td][td]

2​

[/td][td]

Chemistry​

[/td][td]

History​

[/td][td]

Biology​

[/td][td]

Physics​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

3​

[/td][td]

Student 2​

[/td][td]

3​

[/td][td]

4​

[/td][td]

5​

[/td][td]

5​

[/td][td]

History​

[/td][td]

Physics​

[/td][td]

Chemistry​

[/td][td]

Biology​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

4​

[/td][td]

Student 3​

[/td][td]

1​

[/td][td]

0​

[/td][td]

2​

[/td][td]

3​

[/td][td]

Physics​

[/td][td]

History​

[/td][td]

Biology​

[/td][td]

Chemistry​

[/td][/tr]
[/table]

Array formula in F2 copied across till I2 and down
=INDEX($B$1:$E$1,SMALL(IF(ISNA(MATCH($B$1:$E$1,$E2:E2,0)),IF($B2:$E2=LARGE($B2:$E2,COLUMNS($F2:F2)),COLUMN($B2:$E2)-COLUMN($B2)+1)),1))
Ctrl+Shift+Enter

M.

 

[MickG]

Code option :-

[COLOR="Navy"]Sub[/COLOR] MG14Aug26
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, nStr [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String,[/COLOR] Dn [COLOR="Navy"]As[/COLOR] Range, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] S [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Ac [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] R [COLOR="Navy"]As[/COLOR] Range, Sp [COLOR="Navy"]As[/COLOR] Variant, Lst [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
Lst = Cells("2", Columns.Count).End(xlToLeft).Column
[COLOR="Navy"]Set[/COLOR] Rng = Rng.Resize(, Lst)
[COLOR="Navy"]For[/COLOR] n = 2 To Rng.Rows.Count
 nStr = ""
 [COLOR="Navy"]Set[/COLOR] R = Rng.Rows(n).Offset(, 1).Resize(, Rng.Rows(n).Columns.Count - 1)
   c = 0
   [COLOR="Navy"]For[/COLOR] Ac = 1 To Rng.Columns.Count - 1
       c = c + 1
       nStr = nStr & IIf(nStr = "", Application.Large(R, c), "," & Application.Large(R, c))
    [COLOR="Navy"]Next[/COLOR] Ac
Sp = Split(nStr, ",")
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] R.Cells
    [COLOR="Navy"]For[/COLOR] S = 0 To UBound(Sp)
        [COLOR="Navy"]If[/COLOR] CStr(Dn.Value) = Sp(S) [COLOR="Navy"]Then[/COLOR]
             Cells(Dn.Row, R.Columns.Count + 2 + S) = Cells(1, Dn.Column)
             Sp(S) = ""
             [COLOR="Navy"]Exit[/COLOR] For
        [COLOR="Navy"]End[/COLOR] If
   [COLOR="Navy"]Next[/COLOR] S
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]Next[/COLOR] n
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[kimhesk]

Thanks for this, will this code work for 9 columns of grades or would it need to be tweaked? (either VBA or the formula)

 

[MickG]

The code should be good for as many columns as you like.
NB:- I have altered the code slightly because if you try to run the code a second time , the original results will throw an error. With this code you get a msgbox to tell you to remove the results and retry.

[COLOR=navy]Sub[/COLOR] MG14Aug16
[COLOR=navy]Dim[/COLOR] Rng [COLOR=navy]As[/COLOR] Range, nStr [COLOR=navy]As[/COLOR] [COLOR=navy]String,[/COLOR] Dn [COLOR=navy]As[/COLOR] Range, n [COLOR=navy]As[/COLOR] [COLOR=navy]Long,[/COLOR] c [COLOR=navy]As[/COLOR] [COLOR=navy]Long[/COLOR]
[COLOR=navy]Dim[/COLOR] S [COLOR=navy]As[/COLOR] [COLOR=navy]Long,[/COLOR] Ac [COLOR=navy]As[/COLOR] [COLOR=navy]Long,[/COLOR] R [COLOR=navy]As[/COLOR] Range, Sp [COLOR=navy]As[/COLOR] Variant, Lst [COLOR=navy]As[/COLOR] [COLOR=navy]Long[/COLOR]
[COLOR=navy]Set[/COLOR] Rng = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
Lst = Cells("2", Columns.Count).End(xlToLeft).Column
[COLOR=navy]If[/COLOR] IsNumeric(Cells(2, Lst)) [COLOR=navy]Then[/COLOR]
[COLOR=navy]Set[/COLOR] Rng = Rng.Resize(, Lst)
[COLOR=navy]For[/COLOR] n = 2 To Rng.Rows.Count
 nStr = ""
 [COLOR=navy]Set[/COLOR] R = Rng.Rows(n).Offset(, 1).Resize(, Rng.Rows(n).Columns.Count - 1)
   c = 0
   [COLOR=navy]For[/COLOR] Ac = 1 To Rng.Columns.Count - 1
       c = c + 1
       nStr = nStr & IIf(nStr = "", Application.Large(R, c), "," & Application.Large(R, c))
    [COLOR=navy]Next[/COLOR] Ac
Sp = Split(nStr, ",")
[COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] Dn [COLOR=navy]In[/COLOR] R.Cells
    [COLOR=navy]For[/COLOR] S = 0 To UBound(Sp)
        [COLOR=navy]If[/COLOR] CStr(Dn.Value) = Sp(S) [COLOR=navy]Then[/COLOR]
             Cells(Dn.Row, R.Columns.Count + 2 + S) = Cells(1, Dn.Column)
             Sp(S) = ""
             [COLOR=navy]Exit[/COLOR] For
        [COLOR=navy]End[/COLOR] If
   [COLOR=navy]Next[/COLOR] S
[COLOR=navy]Next[/COLOR] Dn
[COLOR=navy]Next[/COLOR] n
[COLOR=navy]Else[/COLOR]
 MsgBox "Remove Results and retry"
[COLOR=navy]End[/COLOR] [COLOR=navy]If[/COLOR]
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]

Regards Mick

PS:-As Marcelo spotted the Variable "Lst" is used to get the last Column in your data.

 

[Marcelo Branco]

Not tested, but i think the code provided by Mick would work perfectly - he used a variable (Lst) to get the last column with data. Am i right, Mick?

In my formula the ranges must be adjusted to suit.

M.