+ Reply to Thread
Results 1 to 7 of 7

Find the large values with multiple criteria and with duplicates

  1. #1
    Registered User
    Join Date
    12-29-2019
    Location
    Bahrain
    MS-Off Ver
    Office 365
    Posts
    10

    Find the large values with multiple criteria and with duplicates

    Hi Experts,


    Looking for a help, as I have a requirement to find out the 3 largest value from a table which is attached.
    My selection is dynamic which are the “Month” and “Category”

    I have put a “large” formula combined with “Iferror” which was working fine but stuck in two cases.
    • Duplicate values ( which is to come as the next position)
    • Don’t know how to retrieve the name matching to the resulted value, which is to shown in the adjacent column.

    I have put the desired result also. I have highlighted the duplicate values in each month for your easy identification.
    I am using Office 365.

    Thanks in advance for the help.
    Attached Files Attached Files

  2. #2
    Forum Guru Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,191

    Re: Find the large values with multiple criteria and with duplicates

    A slightl different approach: you can use Power Query (Get & Transform Data) to normalise your data, then use a pivot table to report as required.

    Format your source data as a table, then use query code:

    Please Login or Register  to view this content.
    Close and Load To Data Model.

    Insert Pivot Table based on Data Model, with:
    Filters: Month, Category
    Rows: Name
    Values: Value

    Sort Name > Descending by Value.
    Filter Name > Value Filters > Top N > Top 3 Items by Value

    Example attached.
    Attached Files Attached Files
    let Source = #table({"Question","Thread", "User"},{{"Answered","Mark Solved", "Add Reputation"}}) in Source

    If I give you Power Query (Get & Transform Data) code, and you don't know what to do with it, then CLICK HERE

    Walking the tightrope between genius and eejit...

  3. #3
    Registered User
    Join Date
    12-29-2019
    Location
    Bahrain
    MS-Off Ver
    Office 365
    Posts
    10

    Re: Find the large values with multiple criteria and with duplicates

    Hi Olly,


    Thanks for the quick response and for the amazing result.
    May I ask you that other than power query, is there any way to get this done with the same result.

    Because, I am not familiar with power query, But I handle it once you have the patience to guide me the step by step actions to get this done through power query.

  4. #4
    Forum Guru
    Join Date
    02-27-2016
    Location
    Vietnam
    MS-Off Ver
    2010
    Posts
    3,947

    Re: Find the large values with multiple criteria and with duplicates

    Quote Originally Posted by smohan7042 View Post
    is there any way to get this done with the same result.
    Try this in N9:

    Please Login or Register  to view this content.

  5. #5
    Forum Guru Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,191

    Re: Find the large values with multiple criteria and with duplicates

    There's a link in my signature which guides you through how to use Power Query code provided.

  6. #6
    Registered User
    Join Date
    12-29-2019
    Location
    Bahrain
    MS-Off Ver
    Office 365
    Posts
    10

    Re: Find the large values with multiple criteria and with duplicates

    Thanks Olly.
    I will go through it definitely.

  7. #7
    Registered User
    Join Date
    12-29-2019
    Location
    Bahrain
    MS-Off Ver
    Office 365
    Posts
    10

    Re: Find the large values with multiple criteria and with duplicates

    Quote Originally Posted by Phuocam View Post
    Try this in N9:

    Please Login or Register  to view this content.
    Thanks Phuocam,

    It is working fine as it should.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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