Custom Formula based on Rank Remarks

[analyst]

I've sheet1 where Students Name, Marks, Passing Status etc (Pass, Fail, ATKT) are given based on some Ranking system, for current Year (Range A19:F73), and for last 5 years (Range H19:M73).

Row data which would always starts from ROW18, could increase/decrease in future based on number of students.

Row 1 to 17 is like DASHBOARD, where I want summary of students who have passed, or failed based on Score Rank (this is in Column E & L respectively),

So, those who have 'Pass' with high 'Score Rank' his name, total mark, and SCORE RANK should be part of DASHBOARD
So is the case with those who have 'Fail' with High 'Score Rank'

Currently, I've done it manually in DASHBOARD to show how the final output should look like.

What formula should I have in place in each Matrix (A3:C17), (D3:F17), (H3:J17), and (K3:M17) to get above

Sample file is attached.

I dont know which formula should i use like, LARGE, OFFSET, IF(AND) or combination to get the desired result.

P.S.: If possible, kindly make formula flexible, so that, in case i need similar table for those who have remarks 'ATKT' can also be made similarly.

[analyst]

Any help here :confused:

[analyst]

Should I modify the query to have VBA macro, instead of having Formula in place, Here only?

[alansidman]

Here is how I would do it.

1. Filter Row 19.
2. Set Remarks to Pass
3. Highlight Column the cells in column A, holding down the Control key, do the same for columns B and E
4. Copy the highlighted cells and paste into A3.
5. Repeat this process for the remaining three items.

Alan

[analyst]

@ alansidman,
Agree , but doing this again and again for four tables is cumbersome, with other routine works on daily basis.

So, using some nested formula, IF each column could bring relevant information based on key criteria, and pulls corresponding data using, either MATCH or INDEX formula, some time can be saved.

Alternatively, if I have a VBA code to filter on those criteria from Row 18, copy top 10 or 15 records and paste as values in summary table at top of the sheet would be nice.

[snb]

I agree: the best thing you can do in this case is to write a macro.

[analyst]

Logically what I've done is to filter data table from row 19. From Column A, select down filtered rows, and copy data and paste special as values from Row A3. Later, using nested formula Index and MATCH function, i'm pulling corresponding data for Column B, and Column C.

My current code is appended below.

My revised limited query is;
1) When filter is applied, i want to select only first 10 rows, (lesser accpetable, if no data) and paste. Currently, all filterd data would get copied and in summary/dashboard, i want to paste only first 10 data only. So, how to restrict selection in filter mode only to 10 rows FOR 'copy' purpose.

2) For students who are being marked as 'Fail', their score rank is negative numbers, arranged descending. So larger negative number would appear last, instead of top. This order would be good for those who have passed and have positive Score Rank.

Kindly help.

Current code is


Sub filterf()

' filterf Macro

Rows("19:19").Select
Selection.AutoFilter
Selection.AutoFilter Field:=6, Criteria1:="Pass"
'Range("A20").Select
'Range(Selection, Selection.End(xlToRight)).Select
'Range(Selection, Selection.End(xlDown)).Select
'Selection.Copy
Range("A20").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Range("A3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Selection.AutoFilter
Rows("19:19").Select
Selection.AutoFilter
Selection.AutoFilter Field:=6, Criteria1:="Fail"
Range("A20").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Range("D3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Selection.AutoFilter
End Sub


Sample workbook attached.

[analyst]

Does above make sense? Is it too difficult? Or having seen no answer, should i close this thread?

Seek, Senior's guidance.

[analyst]

I think moderators are not around, and few ill motivated members are spamming the forum creating annoyance.

[LalitPandey87]

Try this:

For Current Year Table (Change Total Mark Column header to Total Marks)

Step 1:
In Cell A3 put below formula and Press enter then select A3:A17 and press Ctrl + Shift + Enter:



=IFERROR(INDEX($A$20:$F$73,IFERROR(SMALL(IF(($F$20 :$F$73="Pass"),(ROW($A$20:$A$73)-ROW($A$20))+1,""),ROW(INDIRECT("1:" & COUNTA($A$20:$A$73)))),""),MATCH(A$2,$A$19:$F$19,0)),"")


Select range A3:A17 and drag it to next two columns B3:B17 and C3:C17

Step 2:
In Cell D3 put below formula and Press enter then select D3:D17 and press Ctrl + Shift + Enter:



=IFERROR(INDEX($A$20:$F$73,IFERROR(SMALL(IF(($F$20 :$F$73="Fail"),(ROW($A$20:$A$73)-ROW($A$20))+1,""),ROW(INDIRECT("1:" & COUNTA($A$20:$A$73)))),""),MATCH(D$2,$A$19:$F$19,0)),"")


Select range D3:D17 and drag it to next two columns E3:E17 and F3:F17

For 5 Year Table (Change Total Mark Column header to Total Marks)

Step 1:
In Cell H3 put below formula and Press enter then select H3:H17 and press Ctrl + Shift + Enter:



=IFERROR(INDEX($H$20:$M$73,IFERROR(SMALL(IF(($M$20 :$M$73="Pass"),(ROW($H$20:$H$73)-ROW($H$20))+1,""),ROW(INDIRECT("1:" & COUNTA($H$20:$H$73)))),""),MATCH(A$2,$H$19:$M$19,0)),"")
Select range H3:H17 and drag it to next two columns I3:I17 and J3:J17

Step 2:
In Cell K3 put below formula and Press enter then select K3:K17 and press Ctrl + Shift + Enter:



=IFERROR(INDEX($H$20:$M$73,IFERROR(SMALL(IF(($M$20 :$M$73="Fail"),(ROW($H$20:$H$73)-ROW($H$20))+1,""),ROW(INDIRECT("1:" & COUNTA($H$20:$H$73)))),""),MATCH(D$2,$H$19:$M$19,0)),"")


Select range K3:K17 and drag it to next two columns L3:L17 and M3:M17

[analyst]

Thanks Lalit. I did as you suggested, but in every cell, there is an error - #NAME?.

Revised book is attached. Please guide/do the needful. Thanks a ton for suggestions.

No idea, where i've misdirected myself!

[analyst]

I'm closing this thread, as it is. Thanks for all the support, fellow members. I'm also exiting from this forum, trust no offence.

Once again thanks for all the support that you all extended to me.

Kindly suggest, how do i delete my profile / Deactivate My Account from this Forum

I'll be thankful to the moderators too, if they can directly delete my account/membership, without intimation to me.

[LalitPandey87]

All formulas are array formula so what is missing in your file is:

First select the range A3:A17 Press F2 and then Ctrl + Shift + Enter

Try this and it will solve your problem.

[analyst]

@Lalit,

Thanks Brother. I did Ctrl+Shift+Enter to effect formula as Array Formula, but still error persist.

[LalitPandey87]

Although the solution which i provided you is perfectly working for me, i think the only issue is you are not familiar with array formula so you can google it.
Here i am posting a new solution based on array formula :) with some changes

Try this:

For Current Year Table (Change Total Mark Column header to Total Marks)

Step 1:
In Cell A3 put below formula and press Ctrl + Shift + Enter and now drag A3 formula down till you need it



=IFERROR(INDEX($A$20:$F$73,SMALL(IF(($F$20:$F$73="Pass"),(ROW($A$20:$A$73)-ROW($A$20))+1,""),ROWS(A$3:A3)),MATCH(A$2,$A$19:$F$19,0)),"")


Now drag the selected cells(where you draged the formula) for next two colums which are Total Marks and Score Rank

Step 2:
In Cell D3 put below formula and press Ctrl + Shift + Enter and now drag D3 formula down till you need it



=IFERROR(INDEX($A$20:$F$73,SMALL(IF(($F$20:$F$73="Fail"),(ROW($A$20:$A$73)-ROW($A$20))+1,""),ROWS(D$3:D3)),MATCH(D$2,$A$19:$F$19,0)),"")


Now drag the selected cells(where you draged the formula) for next two colums which are Total Marks and Score Rank

For 5 Year Table (Change Total Mark Column header to Total Marks)

Step 1:
In Cell H3 put below formula and press Ctrl + Shift + Enter and now drag H3 formula down till you need it



=IFERROR(INDEX($H$20:$M$73,SMALL(IF(($M$20:$M$73="Pass"),(ROW($H$20:$H$73)-ROW($H$20))+1,""),ROWS(D$3:D3)),MATCH(D$2,$H$19:$M$19,0)),"")

Now drag the selected cells(where you draged the formula) for next two colums which are Total Marks and Score Rank

Step 2:
In Cell K3 put below formula and press Ctrl + Shift + Enter and now drag K3 formula down till you need it



=IFERROR(INDEX($H$20:$M$73,SMALL(IF(($M$20:$M$73="Fail"),(ROW($H$20:$H$73)-ROW($H$20))+1,""),ROWS(D$3:D3)),MATCH(D$2,$H$19:$M$19,0)),"")


Now drag the selected cells(where you draged the formula) for next two colums which are Total Marks and Score Rank


As all these are array formula so make sure you are applying the formula with Ctrl + Shift + Enter