View original (French)

Compare 2 pricing grids

MadameFanny Posted messages 6 Status Member -  
Raymond PENTIER Posted messages 58216 Registration date   Status Contributor Last intervention   -
Hello.
First of all, I'm sorry to ask a question if it has already been answered, but I can't find exactly the same situation as mine.
So, I'm working on Excel, and I have, in particular, transport prices by Department/Number of pallets.
How can I easily compare my different service providers?
Thank you for your help.
Fanny

5 answers

  1.
    DjiDji59430 Posted messages 4278 Registration date   Status Member Last intervention   717
     
    Hello,
    When you attach a test file (with ALL the explanations or desired results) or better yet, if possible, the concerned file, it is more convenient for the participants. And you have a better chance that someone will take an interest in your problem.

    1) Go to https://www.cjoint.com/
    2) Click on [Browse] to select your file
    3) Scroll down to the bottom of the page to click on [Create Cjoint link]
    4) After a few seconds, the link will appear in blue underlined; select it and do "Copy"
    5) Return to the discussion, and in your reply message, do "Paste".

    Best regards.
    0
  3. DjiDji59430 Posted messages 4278 Registration date   Status Member Last intervention   717
     
    Hello,

    A test:

    https://www.cjoint.com/c/FJAp5ryrPYr

    Regards
    0
    1. MadameFanny Posted messages 6 Status Member
       
      Hello DjiDji59430. Thank you very much for the time spent. It's a great calculation that will help me quickly check the differences, it's awesome.
      Is it possible to add multiple carriers?
      Is it possible for all the prices for the same department to appear?
      0
      1. DjiDji59430 Posted messages 4278 Registration date   Status Member Last intervention   717 > MadameFanny Posted messages 6 Status Member
         
        Hello,

        "Is it possible to add multiple carriers?"
        Yes, provided that the sheets have identical presentations and are named after the carrier.
        "Is it possible for all prices for the same department to appear?"
        What do you mean? Regardless of the number of pallets (in other words, you select a department, there will be as many lines as there are carriers, and you choose the cheapest for your delivery? (the cheapest for 25 is not necessarily the cheapest for 50)

        Please add a handwritten sheet with exactly what you would like
        Best regards.
        0
    2. MadameFanny Posted messages 6 Status Member
       
      Thank you. I will revise my pricing grids and make sure they have the same presentation.
      0
  4. Raymond PENTIER Posted messages 58216 Registration date   Status Contributor Last intervention   17 482
     
    Hello Fanny.

    "I would like to know if it is possible to get a table that would allow for a price comparison. Maybe we need to combine these two grids into one?"
    Probably yes; it is not necessary to have a single grid (by the way, how would you go about it?), but we should place both sheets in the same file (workbook), and have the A columns identical (same number of rows, same data).
    And what display would you like in the comparison table: the lowest value or the name of the cheapest supplier?

    In the meantime, the solution from DjiDji59430 (whom I salute) is very good!
    We could consider adding drop-down lists in cells E3 and E5, but that is really not mandatory.
    You will note that he gathered all the sheets into a single file...

    Best regards.
    --
    It’s nice, retirement! Especially in the Caribbean... :-)
    Raymond (INSA, AFPA, CF/R)
    0
    1. MadameFanny Posted messages 6 Status Member
       
      Hello Raymond.
      DjiDji59430's solution is really good even though I still asked him 2 questions to refine my situation.
      I am very impressed by the possibilities that Excel offers and very grateful that people take the time to solve the problems of strangers.
      To answer your question, I'm interested in the lowest value, as I need to quickly calculate the redelivery costs in France, based on the arrival location in the territory.
      0
  6. Raymond PENTIER Posted messages 58216 Registration date   Status Contributor Last intervention   17 482
     
    Hello DjiDji59430.
    Hello Fanny.

    In order to complete my reflection "And what would you like to display in the comparison table: the lowest value or the name of the lowest bidder supplier?", and although the issue is resolved, I am submitting my proposal to both of you with a table of the same dimensions as the first two:
    Each cell displays the lowest value between the 2 providers using the MIN() function, and the lowest bidder is highlighted in green using Conditional Formatting. https://www.cjoint.com/c/FJBouXWWpfs 
     Attention: modified link! ...
    Best regards.
    Retirement is great! Especially in the Antilles... :-)
    Raymond (INSA, AFPA, CF/R)
    0
    1. MadameFanny Posted messages 6 Status Member
       
      Hello Raymond.
      I just saw your reply.
      When I open your table, all the cells have a "purple" background, I don't have any cell in green. How can I tell which carrier corresponds to the price in the cell?
      0
      1. Raymond PENTIER Posted messages 58216 Registration date   Status Contributor Last intervention   17 482 > MadameFanny Posted messages 6 Status Member
         
        Sorry!
        It's just that I'm working with Excel 2013, and you're certainly using Excel 2003...
        The conditional formatting rules are not the same!
        My conditional formatting formula references 2 other sheets; apparently it doesn't work with the older version.
        Hurry up and switch to a less outdated version of Excel...
        0