+ Reply to Thread
Results 1 to 16 of 16

Need to Bring in MAX amount based on multiple criteria.

  1. #1
    Registered User
    Join Date
    05-23-2022
    Location
    Virginia, United States
    MS-Off Ver
    2016+
    Posts
    19

    Lightbulb Need to Bring in MAX amount based on multiple criteria.

    Columns (1-4) are headers

    1) Name
    2) Data
    3) Unique ID
    4) $

    I would like to show the Max $ amount assigned to Data. Unique ID is based off Data and Name (1) is added if Data and Name differ. Currently I've singled out the Data through unique formula and power pivot, but many "Data names" have multiple spellings. I can easily identify multiple spellings with power pivot, but trying to have dynamic display for reporting.

    Is there an index/match formula nested within a If formula that can identify all the "Data names" as being the same if containing "x" and then bring back Max Value?

    -Statements-
    If Data (B2) and ID (C2) = B1/C1 then Sum D1:D2
    IF Data (B2) and ID (C2) does not equal then sum D2 ****

    ****If B1 and B2 both have same "Data name" but different ID show MAX value based of data name - in 14 days another "Data name" could come up that is identical (different creation date/ID) - if that is the new MAX then show that value.

    On my attached worksheet - Examples "The Bird Perch" "Warhorse" and "Riverside" are prime examples of the above request.
    Attached Files Attached Files

  2. #2
    Administrator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 365 Win 11 Home 64 Bit
    Posts
    21,709

    Re: Need to Bring in MAX amount based on multiple criteria.

    I hope I am understanding your needs. Here is my solution. If this is not what you are looking for, then suggest you post your mocked up solution for us

    Excel 2016 (Windows) 64 bit
    F
    G
    1
    Data
    Max Value
    2
    The Bird Perch - Ashville
    2112
    3
    Brightmore
    1263
    4
    CP Grand
    139
    5
    Daven Pointe
    672
    6
    Warhorse
    2925
    7
    23782 - Warhorse - Colorado
    665
    8
    Warhorse - Omaha
    420
    9
    Varnish Old Town
    1150
    10
    Underground ATL Food Hall
    1008
    11
    The Reston
    2048
    12
    Reston
    2048
    13
    KC Braces
    0
    14
    The Bird Perch
    2448
    15
    Riverside Country Club
    105
    16
    Riverside Golf Club
    840
    17
    Moxy Madison
    406
    18
    Sal's Pizza
    435
    19
    Children's Museum Bloom
    336
    20
    Alta Naco
    952
    21
    LD-1111 Alta Naco
    945
    22
    1299 N. California - 61796
    162
    23
    1300 N. California - 61796
    145
    24
    1301 N. California - 61796
    135
    Sheet: Sheet1

    solved with power Query

    Please Login or Register  to view this content.
    Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

    It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

    - Follow this link to learn how to install Power Query in Excel 2010 / 2013.

    - Follow this link for an introduction to Power Query functionality.

    - Follow this link for a video which demonstrates how to use Power Query code provided.
    Attached Files Attached Files
    Alan


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  3. #3
    Registered User
    Join Date
    05-23-2022
    Location
    Virginia, United States
    MS-Off Ver
    2016+
    Posts
    19

    Lightbulb Re: Need to Bring in MAX amount based on multiple criteria.

    I have updated the worksheet that I added with additional examples.

    The solution you shared was something "similar" to what I produced; only difference is that I went through the Power Editor and not formula. This might not be possible - but (Fingers crossed).

    Let me know if there is anything else I can supply for clarification.
    Attached Files Attached Files

  4. #4
    Administrator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 365 Win 11 Home 64 Bit
    Posts
    21,709

    Re: Need to Bring in MAX amount based on multiple criteria.

    Understand what the requirements are. Dinner guests arriving in a couple of minutes. Will look at later and work on it if no one else has a solution.

  5. #5
    Registered User
    Join Date
    05-23-2022
    Location
    Virginia, United States
    MS-Off Ver
    2016+
    Posts
    19

    Re: Need to Bring in MAX amount based on multiple criteria.

    Appreciate and enjoy the dinner!

  6. #6
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 2016 (Windows)
    Posts
    5,352

    Re: Need to Bring in MAX amount based on multiple criteria.

    Try the attached - I created a lookup table to standardize the names used - never a good data practice to not control key values
    Attached Files Attached Files
    Bernie Deitrick
    Excel MVP 2000-2010

  7. #7
    Registered User
    Join Date
    05-23-2022
    Location
    Virginia, United States
    MS-Off Ver
    2016+
    Posts
    19

    Re: Need to Bring in MAX amount based on multiple criteria.

    Completely agree Bernie - that's why my own file has a "companion" column that is identical to what you sent . I try not to put my "knowledge" in a question - staying humble means people like you help and I don't confuse with additional information.

    I'm not well versed in Lookup Tables - I'm researching now - what formula did you use to create?

  8. #8
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 2016 (Windows)
    Posts
    5,352

    Re: Need to Bring in MAX amount based on multiple criteria.

    I copied the name column and used 'remove duplicates' to get the unique values. Then I copied that into the column to the right, and corrected the names to standard names. Not sure that I got them the way you want.... but it worked.

    Depending on your version of Excel, you could use UNIQUE to get the list, then copy and paste values to get the fixed names.

  9. #9
    Registered User
    Join Date
    05-23-2022
    Location
    Virginia, United States
    MS-Off Ver
    2016+
    Posts
    19

    Re: Need to Bring in MAX amount based on multiple criteria.

    Apologies - I meant the values - when I complete the lookup either (V or X) I get a spill because of multiple instances. How did you isolate the Max Summed Value?

  10. #10
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 2016 (Windows)
    Posts
    5,352

    Re: Need to Bring in MAX amount based on multiple criteria.

    I find the Max for each name by using the Group By operation, using Max. Did you try the query in the workbook I uploaded?

    Capture.JPG

  11. #11
    Registered User
    Join Date
    05-23-2022
    Location
    Virginia, United States
    MS-Off Ver
    2016+
    Posts
    19

    Re: Need to Bring in MAX amount based on multiple criteria.

    There is a column name "Sum" in your above example - but the worksheet you sent does not have a column with that name. Did you make a sum column? I feel like I'm missing an easy part.

  12. #12
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 2016 (Windows)
    Posts
    5,352

    Re: Need to Bring in MAX amount based on multiple criteria.

    The worksheet does not have it - the PQ creates it by grouping $ by Corrected Name and ID. You should follow the links provided by Alan in the first reply to your post that give information on Power Query.

  13. #13
    Registered User
    Join Date
    05-23-2022
    Location
    Virginia, United States
    MS-Off Ver
    2016+
    Posts
    19

    Re: Need to Bring in MAX amount based on multiple criteria.

    I appreciate the pointers - I'm familiar with the concepts presented by Alan and yourself. My difficulty lies in replicating your method based solely off your results. PQ when grouping, from data Query 3, based on $, ID, and Corrected name in advanced Grouping options it produces the SUM column but it replicates the same information that is currently present in the $ column. The code that Alan gave me above does not bring in your results. The issue is the first Grouping your running in PQ - what were the parameters for that grouping to get the sum column and then your previous post has the secondary grouping constraints.

  14. #14
    Registered User
    Join Date
    05-23-2022
    Location
    Virginia, United States
    MS-Off Ver
    2016+
    Posts
    19

    Re: Need to Bring in MAX amount based on multiple criteria.

    I just accessed the PQ editor from the first download. I'm not sure why I didn't see it thanks

  15. #15
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 2016 (Windows)
    Posts
    5,352

    Re: Need to Bring in MAX amount based on multiple criteria.

    The first thing I did was create a Corrected Name column using VLOOKUPs against the new table of bad/good names.

    Then I started the query:

    let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Data Name", type text}, {"ID", Int64.Type}, {"$", Int64.Type}, {"Corrected name", type text}}),

    And I removed the 'bad' Names column

    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Name"}),

    Then I grouped by Corrected Name and ID, summing $ (and creating "Sum")

    #"Grouped Rows" = Table.Group(#"Removed Columns", {"Corrected name", "ID"}, {{"Sum", each List.Sum([#"$"]), type number}}),

    I sorted - (which isn't strictly needed here or later when I did it again):

    #"Sorted Rows" = Table.Sort(#"Grouped Rows",{{"Corrected name", Order.Ascending}, {"Sum", Order.Descending}}),

    Then I removed the ID column:

    #"Removed Columns1" = Table.RemoveColumns(#"Sorted Rows",{"ID"}),

    Then I Grouped using "Corrected Name" and the Maximum of Sum - creating "Max Sum" (that was the picture I uploaded):

    #"Grouped Rows1" = Table.Group(#"Removed Columns1", {"Corrected name"}, {{"Max Sum", each List.Max([Sum]), type number}}),

    Then I sorted again - not needed, but....

    #"Sorted Rows1" = Table.Sort(#"Grouped Rows1",{{"Corrected name", Order.Ascending}})
    in
    #"Sorted Rows1"

  16. #16
    Registered User
    Join Date
    05-23-2022
    Location
    Virginia, United States
    MS-Off Ver
    2016+
    Posts
    19

    Re: Need to Bring in MAX amount based on multiple criteria.

    Appreciate the help y'all - marking as completed. I have to work in it a bit and then refine as usual. Have a great one

+ 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. Bring value based on criteria from multiple variable number of sheets
    By Inese in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-19-2022, 08:39 PM
  2. Macro to bring back rows from multiple sheets to one master sheet based on criteria
    By Fraser1605 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-02-2021, 01:18 PM
  3. [SOLVED] Calculation with multiple criteria- CAP Amount, Min. Amount etc.
    By Shareez Saleem in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 02-15-2021, 07:59 AM
  4. [SOLVED] Top 3 products based on multiple criteria (Name and Amount)
    By blak9 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-13-2020, 07:42 AM
  5. [SOLVED] Lookup on multiple criteria to bring multiple results
    By daveb86 in forum Excel Formulas & Functions
    Replies: 13
    Last Post: 01-18-2019, 10:35 AM
  6. [SOLVED] Return total amount values based on multiple criteria
    By MyStix01 in forum Excel General
    Replies: 3
    Last Post: 10-12-2017, 09:05 AM
  7. Bring in data from one tab based on two sets of criteria on a different tab
    By tommypkoch in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 11-26-2013, 02:00 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

X vBulletin 4.1.8 Debug Information

  • Page Generation 0.09593 seconds
  • Memory Usage 9,403KB
  • Queries Executed 16 (?)
More Information
Template Usage (35):
  • (1)SHOWTHREAD
  • (1)ad_footer_end
  • (1)ad_footer_start
  • (1)ad_global_above_footer
  • (1)ad_global_below_navbar
  • (1)ad_global_header1
  • (1)ad_global_header2
  • (1)ad_navbar_below
  • (1)ad_showthread_firstpost_sig
  • (1)ad_showthread_firstpost_start
  • (1)ad_thread_first_post_content
  • (1)ad_thread_last_post_content
  • (1)bbcode_code
  • (1)footer
  • (1)forumrules
  • (1)gobutton
  • (1)header
  • (1)headinclude
  • (1)headinclude_bottom
  • (16)memberaction_dropdown
  • (1)navbar
  • (4)navbar_link
  • (4)navbar_noticebit
  • (6)option
  • (4)postbit_attachment
  • (16)postbit_legacy
  • (16)postbit_onlinestatus
  • (16)postbit_wrapper
  • (4)showthread_bookmarksite
  • (7)showthread_similarthreadbit
  • (1)showthread_similarthreads
  • (1)spacer_close
  • (1)spacer_open
  • (1)tagbit_wrapper
  • (1)vbseo_linkbackmenu_entry 

Phrase Groups Available (6):
  • global
  • inlinemod
  • postbit
  • posting
  • reputationlevel
  • showthread
Included Files (40):
  • ./vbseo.php
  • ./env.php
  • ./vbseo/includes/functions_vbseo.php
  • ./vbseo/includes/functions_vbseo_pre.php
  • ./vbseo/includes/functions_vbseo_url.php
  • ./vbseo/includes/functions_vbseo_createurl.php
  • ./vbseo/includes/functions_vbseo_db.php
  • ./vbseo/includes/functions_vbseo_vb.php
  • ./vbseo/includes/functions_vbseo_seo.php
  • ./vbseo/includes/functions_vbseo_misc.php
  • ./vbseo/includes/functions_vbseo_crr.php
  • ./vbseo/includes/functions_vbseo_cache.php
  • ./vbseo/includes/functions_vbseo_hook.php
  • ./vbseo/includes/functions_vbseo_startup.php
  • ./includes/config.php
  • ./showthread.php
  • ./global.php
  • ./includes/class_bootstrap.php
  • ./includes/init.php
  • ./includes/class_core.php
  • ./includes/functions.php
  • ./includes/class_friendly_url.php
  • ./includes/class_hook.php
  • ./includes/functions_cforum.php
  • ./includes/functions_facebook.php
  • ./includes/functions_bigthree.php
  • ./includes/class_postbit.php
  • ./includes/class_bbcode.php
  • ./includes/functions_reputation.php
  • ./includes/class_bootstrap_framework.php
  • ./vb/vb.php
  • ./vb/phrase.php
  • ./packages/vbattach/attach.php
  • ./vb/types.php
  • ./vb/cache.php
  • ./vb/cache/db.php
  • ./vb/cache/observer/db.php
  • ./vb/cache/observer.php
  • ./includes/functions_notice.php
  • ./packages/vbforum/bbcodehelper/table.php 

Hooks Called (50):
  • init_startup
  • friendlyurl_resolve_class
  • database_pre_fetch_array
  • database_post_fetch_array
  • global_bootstrap_init_start
  • global_bootstrap_init_complete
  • cache_permissions
  • fetch_threadinfo_query
  • fetch_threadinfo
  • fetch_foruminfo
  • global_state_check
  • global_bootstrap_complete
  • global_start
  • style_fetch
  • global_setup_complete
  • showthread_start
  • showthread_getinfo
  • friendlyurl_redirect_canonical
  • showthread_post_start
  • showthread_query_postids
  • showthread_query
  • bbcode_fetch_tags
  • bbcode_create
  • showthread_postbit_create
  • postbit_factory
  • postbit_display_start
  • fetch_musername
  • cache_templates
  • template_register_var
  • parse_templates
  • notices_check_start
  • notices_noticebit
  • process_templates_complete
  • reputation_image
  • postbit_imicons
  • bbcode_parse_start
  • bbcode_parse_complete_precache
  • bbcode_parse_complete
  • postbit_attachment
  • postbit_display_complete
  • memberaction_dropdown
  • tag_fetchbit
  • tag_fetchbit_complete
  • showthread_similarthread_query
  • showthread_similarthreadbit
  • forumrules
  • showthread_bookmarkbit
  • navbits
  • navbits_complete
  • showthread_complete


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1