Getting the top 3 value within different sheet using a button in Macro

  • Hi All.


    I need your help.


    Here is the concept of my assignment.


    I need to use a Macro..


    The problem is


    There are 3 sheet.


    sheet1 -


    [TABLE="width: 147"]

    [tr]


    [td]

    Sam2

    [/td]


    [TD="align: right"]96[/TD]

    [/tr]


    [tr]


    [td]

    Sam2

    [/td]


    [TD="align: right"]95[/TD]

    [/tr]


    [tr]


    [td]

    Sam2

    [/td]


    [TD="align: right"]99[/TD]

    [/tr]


    [tr]


    [td]

    Sam2

    [/td]


    [TD="align: right"]79[/TD]

    [/tr]


    [tr]


    [td]

    Sam2

    [/td]


    [TD="align: right"]78[/TD]

    [/tr]


    [tr]


    [td]

    Sam2

    [/td]


    [TD="align: right"]80[/TD]

    [/tr]


    [/TABLE]



    sheet2
    [TABLE="width: 147"]

    [tr]


    [td]

    Department

    [/td]


    [td]

    Score

    [/td]


    [/tr]


    [tr]


    [td]

    Sam3

    [/td]


    [TD="align: right"]95[/TD]

    [/tr]


    [tr]


    [td]

    Sam3

    [/td]


    [TD="align: right"]95[/TD]

    [/tr]


    [tr]


    [td]

    Sam3

    [/td]


    [TD="align: right"]93[/TD]

    [/tr]


    [tr]


    [td]

    Sam3

    [/td]


    [TD="align: right"]80[/TD]

    [/tr]


    [tr]


    [td]

    Sam3

    [/td]


    [TD="align: right"]87[/TD]

    [/tr]


    [tr]


    [td]

    Sam3

    [/td]


    [TD="align: right"]98[/TD]

    [/tr]


    [/TABLE]


    sheet3 - The output



    I need to use a button here:
    so the out will be:
    [TABLE="width: 96"]

    [tr]


    [TD="width: 64, bgcolor: transparent"]Sam2[/TD]
    [TD="width: 64, bgcolor: transparent, align: right"]99[/TD]

    [/tr]


    [tr]


    [TD="bgcolor: transparent"]Sam2[/TD]
    [TD="bgcolor: transparent, align: right"]96[/TD]

    [/tr]


    [tr]


    [TD="bgcolor: transparent"]Sam2[/TD]
    [TD="bgcolor: transparent, align: right"]95[/TD]

    [/tr]


    [tr]


    [TD="bgcolor: transparent"]Sam3[/TD]
    [TD="bgcolor: transparent, align: right"]98[/TD]

    [/tr]


    [tr]


    [TD="bgcolor: transparent"]Sam3[/TD]
    [TD="bgcolor: transparent, align: right"]95[/TD]

    [/tr]


    [tr]


    [TD="bgcolor: transparent"]Sam3[/TD]
    [TD="bgcolor: transparent, align: right"]95[/TD]

    [/tr]


    [/TABLE]


    Any suggention guys? please help me.

  • Re: Getting the top 3 value within different sheet using a button in Macro


    I just need to get the logic. Actually that is not my assignment, that is only the concept.! Thank you for your help..

  • Re: Getting the top 3 value within different sheet using a button in Macro


    haha :) i need to use macro..


    What if i have 100 column. do i need to check each one of the sheet? that the reason why i need to user macro..

  • Re: Getting the top 3 value within different sheet using a button in Macro


    Quote from lopapa;681378

    I just need to get the logic. Actually that is not my assignment, that is only the concept.! Thank you for your help..


    What do you expect from such a poor info???

  • Re: Getting the top 3 value within different sheet using a button in Macro


    Sounds like this is a class assignment maybe? Either way what you need and why you need a macro isn't well detailed. If you only need the top 3 values from each worksheet, 3 formulas in each sheet will get you that, no matter how many columns/rows you have... =LARGE(array,k) is one function, where the array is the data set you're pulling from and k = what number you want (1 = largest, goes down farther as you increase number)


    So for example you could, on worksheet 1 put
    =LARGE($c$1:$c$1000,1) into one cell,
    =LARGE($c$1:$c$1000,2) into another cell, and
    =LARGE($c$1:$c$1000,3) into a third cell and those cells would give you the top 3 numbers in that column c, regardless of how many columns you had. Then you could have the third sheet reference those cells.


    Again, as jindon said, you wouldn't need a macro. Different functions can already handle that.


    If, however, you had to use one, large is in the list of worksheet.functions that can be used in vba...
    http://msdn.microsoft.com/en-u…f822194(v=office.14).aspx

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!