+ Reply to Thread
Results 1 to 8 of 8

Finding multiple matches in a range and displaying the lowest corresponding price.

  1. 06-22-2017, 03:56 PM #1
    jfrdl
    jfrdl is offline
    Registered User
    Join Date
    06-08-2016
    Location
    Toronto
    MS-Off Ver
    2010
    Posts
    4

    Finding multiple matches in a range and displaying the lowest corresponding price.

    So I've run into this issue that's absolutely driving me crazy. Someone in my office uploaded a pricing file twice without deleting the old file.

    We now have a master file for one customer where there is either one or two duplicates for each part number w/ different prices for each. There are thousands of parts.

    Essentially what I want to do is this: If part number is identical to another part number in a range, take the lowest price between the two or three of them and display in a corresponding column to the right.
    or
    I'm simply going to delete the duplicates after this is done and re-upload the pricing file

    A B C
    BALL-.25 L 3.75
    BALL-.25 L 3.003
    BALL-.25 L 3
    BALL-.375 L 2.74
    BALL-.375 L 2.7391
    BALL-.375 L 3.75
    BALL-.5 L 4.6
    BALL-.5 L 3.6218
    BALL-.5 L 3.62
    BALL-.75 L 4.95
    BALL-.75 L 4.9504
    Attached Files Attached Files
    Last edited by jfrdl; 06-22-2017 at 04:14 PM.
    Reply With QuoteRegister To Reply
  2. 06-22-2017, 04:08 PM #2
    JohnTopley
    JohnTopley is offline
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft Excel for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,206

    Re: Finding multiple matches in a range and displaying the lowest corresponding price.

    Attach a sample workbook (not image).

    Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate.

    Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.
    Reply With QuoteRegister To Reply
  3. 06-22-2017, 04:15 PM #3
    jfrdl
    jfrdl is offline
    Registered User
    Join Date
    06-08-2016
    Location
    Toronto
    MS-Off Ver
    2010
    Posts
    4

    Re: Finding multiple matches in a range and displaying the lowest corresponding price.

    Quote Originally Posted by JohnTopley View Post
    Attach a sample workbook (not image).

    Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate.

    Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.
    Thank you I have added a simplified sample
    Reply With QuoteRegister To Reply
  4. 06-22-2017, 04:19 PM #4
    JohnTopley
    JohnTopley is offline
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft Excel for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,206

    Re: Finding multiple matches in a range and displaying the lowest corresponding price.

    In F2

    =MIN(IF($B$2:$B$19=B2,$D$2:$D$19))


    ...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

    Copy down
    Reply With QuoteRegister To Reply
  5. 06-22-2017, 04:24 PM #5
    jfrdl
    jfrdl is offline
    Registered User
    Join Date
    06-08-2016
    Location
    Toronto
    MS-Off Ver
    2010
    Posts
    4

    Re: Finding multiple matches in a range and displaying the lowest corresponding price.

    Quote Originally Posted by JohnTopley View Post
    In F2

    =MIN(IF($B$2:$B$19=B2,$D$2:$D$19))


    ...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

    Copy down
    Beautiful. Worked perfectly, thanks so much!!
    Reply With QuoteRegister To Reply
  6. 06-22-2017, 04:27 PM #6
    JohnTopley
    JohnTopley is offline
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft Excel for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,206

    Re: Finding multiple matches in a range and displaying the lowest corresponding price.

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.
    Reply With QuoteRegister To Reply
  7. 06-22-2017, 04:30 PM #7
    Syrkrasi
    Syrkrasi is offline
    Valued Forum Contributor
    Join Date
    12-01-2016
    Location
    Planet Earth
    MS-Off Ver
    95 - 2016
    Posts
    343

    Re: Finding multiple matches in a range and displaying the lowest corresponding price.

    Find the unique Part Name: =IFERROR(INDEX(Part, MATCH(0,COUNTIF($G$1:G1, Part), 0)),"")
    Find the Lowest Number by Part: =IF(COUNTIF(Part,G2),MIN(IF(Part=G2,Price)),"")

    See attachment

    Ensure that you use Shift + Ctrl + Enter then copy calculation down to last unique Part name
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  8. 06-22-2017, 04:43 PM #8
    jfrdl
    jfrdl is offline
    Registered User
    Join Date
    06-08-2016
    Location
    Toronto
    MS-Off Ver
    2010
    Posts
    4

    Re: Finding multiple matches in a range and displaying the lowest corresponding price.

    Quote Originally Posted by Syrkrasi View Post
    Find the unique Part Name: =IFERROR(INDEX(Part, MATCH(0,COUNTIF($G$1:G1, Part), 0)),"")
    Find the Lowest Number by Part: =IF(COUNTIF(Part,G2),MIN(IF(Part=G2,Price)),"")

    See attachment

    Ensure that you use Shift + Ctrl + Enter then copy calculation down to last unique Part name
    Also works perfect. Thanks!
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Comparing my price to the lowest competitor's price in an excel row
    By yr25 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 05-10-2017, 11:05 AM
  2. help with finding and auto updating lowest price for different products
    By shesdetermined24 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-12-2012, 04:27 PM
  3. Displaying the difference of the lowest in a range
    By bobbby1949 in forum Excel General
    Replies: 8
    Last Post: 03-17-2011, 11:19 PM
  4. Excel 2007 : Help finding lowest price in list of orders
    By Snoozeburger in forum Excel General
    Replies: 6
    Last Post: 10-14-2010, 03:05 AM
  5. Finding lowest cost price with multiple criteria
    By jimbokeep in forum Excel General
    Replies: 8
    Last Post: 06-30-2010, 01:11 PM
  6. Formula for displaying in 1 cell Lowest value from multiple other cells
    By [email protected] in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-16-2006, 07:10 PM
  7. [SOLVED] Formula for displaying the lowest number of a range?
    By coal_miner in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-25-2005, 10:06 AM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1