Results 1 to 6 of 6

Thread: Summarize Worksheet

  1. #1
    Neophyte Ayesha Azmi's Avatar
    Join Date
    Apr 2013
    Location
    A, A
    Posts
    2
    Articles
    0

    Summarize Worksheet



    Register for a FREE account, and/
    or Log in to avoid these ads!

    I have an excel sheet with PRICES of various THICKNESSES of Boards from different SUPPLIERS with further SPECS (Glue used, Payment terms, etc.).

    I need a simple and fast way to SUMMARIZE the data to only show me the LOWEST PRICE of EACH THICKNESS with the Suppliers name and all the specs.


    I have tried playing with Pivot Tables but never works and I end up getting the MIN Price of Each Thickness and then MANUALLY have to insert the corresponding data, which is extremely inconvenient as the prices go up and down regularly, so I have to change the the whole table every time.


    I would really appreciate a solution as I AM SURE EXCEL HAS IT but I am just a hard time figuring it out.
    Thanks,


    Ayesha

  2. #2
    Super Moderator JeffreyWeir's Avatar
    Join Date
    Mar 2011
    Location
    New Zealand
    Posts
    357
    Articles
    0
    Ayesha: Can you sort your database by Thickness and then by Price? If so, then you can use a simple VLOOKUP.

    Say you have a database with Thickness in column A, Provider in column B, and Price in column C. In column E, put a unique list of the various thicknesses. (You could use a pivottable to do this, if you have lots of thicknesses). In column F, put this formula:
    =VLOOKUP(E:E,A:C,2,0)
    THat will return the corresponding provider with the cheapest price for the thickness listed in column E.
    In column G, put this formula:
    =VLOOKUP(E:E,A:C,3,0)
    THat will return the corresponding price for the provider with the cheapest price for the thickness listed in column E.

  3. #3
    Super Moderator Pecoflyer's Avatar
    Join Date
    Oct 2011
    Location
    Brussels Belgium
    Posts
    1,461
    Articles
    0
    Excel Version
    2010 on Xubuntu
    Is the attached something you can live with? Be aware formulas are entered with Ctrl+Shift+EnterThickness.xlsx

  4. #4
    Super Moderator JeffreyWeir's Avatar
    Join Date
    Mar 2011
    Location
    New Zealand
    Posts
    357
    Articles
    0
    Pecoflyer: that's a nice solution. Although I'm not sure why you need the IF($B$2:$B$6<>"",$B$2:$B$6) bit in the formula =MIN(IF($A$2:$A$6=D2,(IF($B$2:$B$6<>"",$B$2:$B$6)))) in column E. Can't you just use =MIN(IF($A$2:$A$6=D2,$B$2:$B$6))? Or am I missing something?

  5. #5
    Super Moderator Pecoflyer's Avatar
    Join Date
    Oct 2011
    Location
    Brussels Belgium
    Posts
    1,461
    Articles
    0
    Excel Version
    2010 on Xubuntu
    Your remark is totally correct. I just built the <>"" in to account for empty cells. If there are any, the answer could be 0, which is probably not what you want.
    But again, if you have no empty cells you can leave that part out

  6. #6
    Neophyte Ayesha Azmi's Avatar
    Join Date
    Apr 2013
    Location
    A, A
    Posts
    2
    Articles
    0
    I cant thank the both of you enough....you guys have helped me save hours of useless work....THANKS A BILLION.....

    I worked out both the suggestions and learned a lot on the way....I ended up going with Pecoflyer's solution at the end because it didn't require me to filter and sort out the data every time new information was added to it. But at the same time I think the VLOOKUP Function is extremely handy and I will be making good use of it....THANKS AGAIN!!!

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •