Page 1 of 2 12 LastLast
Results 1 to 10 of 15

Thread: Custom Formula based on Rank Remarks

  1. #1
    Member
    Join Date
    Aug 2013
    Posts
    59
    Rep Power
    12

    Custom Formula based on Rank Remarks

    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.
    Attached Files Attached Files
    Last edited by analyst; 06-06-2014 at 01:41 PM.

  2. #2
    Member
    Join Date
    Aug 2013
    Posts
    59
    Rep Power
    12
    Any help here

  3. #3
    Member
    Join Date
    Aug 2013
    Posts
    59
    Rep Power
    12
    Should I modify the query to have VBA macro, instead of having Formula in place, Here only?

  4. #4
    Senior Member alansidman's Avatar
    Join Date
    Apr 2012
    Posts
    125
    Rep Power
    13
    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

  5. #5
    Member
    Join Date
    Aug 2013
    Posts
    59
    Rep Power
    12
    @ 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.

  6. #6
    Senior Member
    Join Date
    Jun 2012
    Posts
    337
    Rep Power
    13
    I agree: the best thing you can do in this case is to write a macro.

  7. #7
    Member
    Join Date
    Aug 2013
    Posts
    59
    Rep Power
    12
    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

    Code:
    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.
    Attached Files Attached Files
    Last edited by analyst; 06-16-2014 at 11:39 AM.

  8. #8
    Member
    Join Date
    Aug 2013
    Posts
    59
    Rep Power
    12
    Does above make sense? Is it too difficult? Or having seen no answer, should i close this thread?

    Seek, Senior's guidance.

  9. #9
    Member
    Join Date
    Aug 2013
    Posts
    59
    Rep Power
    12
    I think moderators are not around, and few ill motivated members are spamming the forum creating annoyance.

  10. #10
    Senior Member LalitPandey87's Avatar
    Join Date
    Sep 2011
    Posts
    222
    Rep Power
    14
    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:

    Code:
    =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:

    Code:
    =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:

    Code:
    =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:

    Code:
    =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

Similar Threads

  1. Replies: 2
    Last Post: 10-23-2013, 12:28 AM
  2. Replies: 13
    Last Post: 07-08-2013, 10:34 AM
  3. Replies: 2
    Last Post: 03-17-2013, 06:07 PM
  4. Rank Function
    By Portucale in forum Access Help
    Replies: 2
    Last Post: 01-23-2013, 11:26 PM
  5. Custom Spin Button Based On Values Passed From Array
    By Preeti Verma in forum Excel Help
    Replies: 7
    Last Post: 05-22-2012, 07:23 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •