+ Reply to Thread
Results 1 to 10 of 10

How to get Index of Top 3 in each Month from Multiple Sheets into Output/Summary Sheet

  1. #1
    Registered User
    Join Date
    12-26-2016
    Location
    SA, KSA
    MS-Off Ver
    2010, 2013, 2016, 2019
    Posts
    44

    How to get Index of Top 3 in each Month from Multiple Sheets into Output/Summary Sheet

    Hi,

    I've a data in Sheets 1 & 2 and I want the Index of Top 3 Items from Sheet 1 and Sheet 2 in each month in output/Summary Sheet.

    I've provided the Sample data and output in the attached workbook.

    if Possible, concatenate the Index item with the item Value in output.

    Appreciate you usual help.
    Attached Files Attached Files

  2. #2
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    8,274

    Re: How to get Index of Top 3 in each Month from Multiple Sheets into Output/Summary Sheet

    What would the results be with duplicate value (in same sheet or different sheet but same month)?

  3. #3
    Registered User
    Join Date
    12-26-2016
    Location
    SA, KSA
    MS-Off Ver
    2010, 2013, 2016, 2019
    Posts
    44
    Quote Originally Posted by bebo021999 View Post
    What would the results be with duplicate value (in same sheet or different sheet but same month)?
    The results would be in different sheet as seen in the attached sample workbook, and the items are unique. No duplicates

  4. #4
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    8,274

    Re: How to get Index of Top 3 in each Month from Multiple Sheets into Output/Summary Sheet

    I mean duplicate value among items.
    For instant: Item 1 = 30, Item 2=30, Item A=30
    Or: Item 1=30, Item B=25, Item 3=25
    Which item is 1st, 2nd and third?

  5. #5
    Forum Contributor
    Join Date
    03-18-2014
    Location
    Singapore
    MS-Off Ver
    Excel 2016 / 2019
    Posts
    251

    Re: How to get Index of Top 3 in each Month from Multiple Sheets into Output/Summary Sheet

    Hi qadeerume,

    Attached Power Query solution

    Basically I brought in your two items table, combine them as one, group them in Months calculate
    their top 3 Rank ( I think if there are same values you will take Item 3 (number) instead)

    then I pivot it with you desire format Item 3 x 25

    Hope it helps
    Attached Files Attached Files
    Christopher Yap

  6. #6
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    8,274

    Re: How to get Index of Top 3 in each Month from Multiple Sheets into Output/Summary Sheet

    Try with formula:

    B10:
    Please Login or Register  to view this content.
    Drag down and accross
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    12-26-2016
    Location
    SA, KSA
    MS-Off Ver
    2010, 2013, 2016, 2019
    Posts
    44

    Re: How to get Index of Top 3 in each Month from Multiple Sheets into Output/Summary Sheet

    @bebo021999,

    output from your formula is incorrect, for the test purpose I changed the Item 1 & Item 2 values to 30 for both in Sheet 1 and Item A Value to 30 for JAN month, the output is coming for JAN as Item 1 x30, Item 1 x 30, Item 1 x 30 which wrong. It should Come as Item 1 x 30, Item 2 x30, and item A x30.

  8. #8
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    8,274

    Re: How to get Index of Top 3 in each Month from Multiple Sheets into Output/Summary Sheet

    Try again:

    Please Login or Register  to view this content.
    **Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).
    Attached Files Attached Files

  9. #9
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    6,859

    Re: How to get Index of Top 3 in each Month from Multiple Sheets into Output/Summary Sheet

    Please try at B10

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Attached Files Attached Files

  10. #10
    Registered User
    Join Date
    12-26-2016
    Location
    SA, KSA
    MS-Off Ver
    2010, 2013, 2016, 2019
    Posts
    44

    Re: How to get Index of Top 3 in each Month from Multiple Sheets into Output/Summary Sheet

    Thank you all of you, @Bo_Ry, @bebo021999, @bluesky63

    all three solutions works, thank you again for your support. Stay blessed.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 3
    Last Post: 11-10-2018, 06:59 AM
  2. Replies: 0
    Last Post: 07-29-2013, 03:00 AM
  3. [SOLVED] Summary sheet - copy cell contents from multiple sheets to one summary sheet
    By jsmity in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-21-2013, 10:32 AM
  4. Create a summary from multiple sheets on a master/summary sheet
    By detribus in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-07-2012, 08:04 PM
  5. Copying a template sheet and putting those multiple sheets data into a summary sheet
    By John Wolfe in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 05-18-2011, 06:29 PM
  6. Summary Sheet help with multiple sheets
    By lacey125 in forum Excel General
    Replies: 1
    Last Post: 09-21-2006, 01:11 PM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1