Summarize Worksheet

Ayesha Azmi

New member
Joined
Apr 6, 2013
Messages
2
Reaction score
0
Points
0
Location
A, A
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
 

JeffreyWeir

Super Moderator
Staff member
Joined
Mar 22, 2011
Messages
357
Reaction score
0
Points
0
Location
New Zealand
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.
 

JeffreyWeir

Super Moderator
Staff member
Joined
Mar 22, 2011
Messages
357
Reaction score
0
Points
0
Location
New Zealand
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?
 

Pecoflyer

Admin Alumnus
Joined
Oct 13, 2011
Messages
1,769
Reaction score
0
Points
36
Location
Brussels Belgium
Excel Version(s)
2010 on Xubuntu - O365
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
 

Ayesha Azmi

New member
Joined
Apr 6, 2013
Messages
2
Reaction score
0
Points
0
Location
A, A
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!!!
 
Top