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

[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