Bringing back column headings for descending values

K

kimhesk

New Member
Joined
Aug 14, 2017
Messages
3
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
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
Sort by date Sort by votes
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!
 
Upvote 0
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.
 
Upvote 0
Code option :-
Code: 
[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
 
Upvote 0
Thanks for this, will this code work for 9 columns of grades or would it need to be tweaked? (either VBA or the formula)
 
Upvote 0
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.
Code: 
[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.
 
Last edited:
Upvote 0
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.
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question Question
VBA to build schedule
Replies
0
Views
372
johnsmith5050
J
GMC The Macro Man
  • Question Question
SEQUENCE formula
Replies
0
Views
106
GMC The Macro Man
GMC The Macro Man
N
  • Solved
How to calculate the percentage?
Replies
9
Views
303
Phuoc
P
A
  • Solved
Return a value after searching a list for any of the terms in another list?
Replies
5
Views
466
Peter_SSs
Peter_SSs
GMC The Macro Man
  • Solved
How to count with Multiple Criteria
Replies
6
Views
228
dreid1011
dreid1011

Forum statistics

Threads
1,223,229
Messages
6,170,881
Members
452,364
Latest member
springate

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top