+ Reply to Thread
Results 1 to 6 of 6

Price Comparison for a Product

  1. #1
    Forum Contributor
    Join Date
    10-18-2016
    Location
    Chicago, USA
    MS-Off Ver
    Office 365
    Posts
    220

    Smile Price Comparison for a Product

    Hi All,

    Hope you are doing well!...Can you please me with this problem below..I am providing the data and the expected output below: I have three columns Product ID,Company and Price ..B&Co is my company..I am trying to compare the product price of my company to other companies and trying to get the difference between B&Co price and the lowest price for this product....Also below the table output that I am expecting...I have attached the excel file too for the same..Note: Some of the company names are blank ...Also I have data for about 1000 products like this...Trying to build the output table as outlined below here:Can you please help!!

    Product ID Company Price

    5678 B&co 4
    Coke 12
    Red rhino 3
    Discountland 3.5

    8945 B&co 10
    Dataalchemy 7
    Unknown 9
    Traveltools 6.5

    9022 50
    B&co 40
    BeezBee 45
    Junipers 48


    Expected output :

    Product ID B&co Price Lowest Price for Product Difference
    5678 4 3 1
    8945 10 6.5 3.5
    9022 40 40 0
    Attached Files Attached Files

  2. #2
    Forum Contributor ANDREAAS's Avatar
    Join Date
    05-06-2013
    Location
    Port Elizabeth, South Africa
    MS-Off Ver
    Excel 2007 / 2010
    Posts
    368

    Re: Price Comparison for a Product

    Hi

    The formulas for what you want is as follows :
    Product ID B&co Price Lowest Price for Product Difference
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    problem is that your amount of companies to compare for each set is variable and that is above my level of expertise......

    You will need a "for each" loop to find each time that B&co appears in column b and that pastes the formulas next to each, the same code will then have to count the amount of companies to compare.

    if you can narrow it down to the same amount of companies to compare to, my solution becomes a little bit more viable, here's how......

    let's say you have B&co and 3 others to compare for each set
    copy the formulas i gave above from column E to H next to the first B&co in your sheet
    if the first B&co is in B2 then the formulas go in E2 to H2
    then select E2 to H5 and at the bottom corner of H5, when you hover your mouse there it should show a +, click and hold that and drag it down all the way to your last comparisons.
    then filter your sheet by B&co and you should have all the comparisons

    if this is something that you do regularly, i'd record a macro for this process, but your best bet is to also consult one of the awesome experts here.....as i said.....my knowledge is very limited.

  3. #3
    Forum Expert
    Join Date
    09-25-2015
    Location
    Milan Italy
    MS-Off Ver
    office 365
    Posts
    1,776

    Re: Price Comparison for a Product

    K5=SUMIFS($C$5:C$18,$A$5:$A$18,$J5,$B$5:$B$18,$K$4) copy down

    L5=AGGREGATE(15,6,($C$5:$C$18)/($A$5:$A$18=$J5),1) copy down

  4. #4
    Forum Contributor
    Join Date
    10-18-2016
    Location
    Chicago, USA
    MS-Off Ver
    Office 365
    Posts
    220

    Re: Price Comparison for a Product

    Thanks Caracalla!..Appreciate your response!..

  5. #5
    Forum Contributor
    Join Date
    10-18-2016
    Location
    Chicago, USA
    MS-Off Ver
    Office 365
    Posts
    220

    Re: Price Comparison for a Product

    Thanks Andreaas!..Appreciate your response!...

  6. #6
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,401

    Re: Price Comparison for a Product

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

+ 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: 9
    Last Post: 11-13-2017, 04:33 AM
  2. Product Comparison
    By DanPa in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 08-17-2017, 05:14 PM
  3. Price comparison (+ or -) formula
    By mlopez60120 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 09-20-2016, 04:56 PM
  4. [SOLVED] Excel Cell To Show Product Price But With A Minimum Price
    By icemoose in forum Excel General
    Replies: 6
    Last Post: 05-04-2016, 06:46 AM
  5. How to automate weekly price updates from diff. suppliers into 1 price comparison sheet
    By blindside21 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 01-09-2014, 02:24 PM
  6. Replies: 1
    Last Post: 07-24-2013, 12:13 PM
  7. Product comparison
    By kolonel in forum Excel General
    Replies: 2
    Last Post: 01-08-2013, 12:25 AM

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