# Excel - Finding the minimum number while comparing two columns

Discussion in 'Business Applications' started by ekim256, Oct 12, 2008.

Not open for further replies.

Joined:
Oct 12, 2008
Messages:
4
Hello,
I am trying to find the minimum number out of a set that has a certain label entered beside it. For example:
Number of OrdersCompany
993 BananaCorp
1200 ZoomZoom
500 Banana Corp
43664 Omletted Way
545 Tomato Inc

I want to find the least amount of orders taken from BananaCorp

Thanks!!!

Joined:
Mar 27, 2008
Messages:
6,553
First Name:
Hans
You have two columns, Orders , Company

Orders Company
500 Banana Corp
993 Banana Corp
43664 Omletted Way
545 Tomato Inc
1200 ZoomZoom

SOrt on Company as a first key and Order as a 2nd Key.
for a visible max order per company.
It's a beginning.

Joined:
Mar 27, 2008
Messages:
6,553
First Name:
Hans
If it's the MAx you want it's the last order, if it's the MIN you want it's the first, Dependunpin upin the sort for irders its is ascending or descending you can alter it.
If you only want to see the MIN or the MAX then you will have to puzzle some more

4. ### slurpee55

Joined:
Oct 20, 2004
Messages:
7,837
This will work to show you what you want, although there is probably a better answer that I haven't figured out.
First, do a Pivot Table on the names of your companies, so you get a listing of each company one time. Highlight the names in the pivot table and copy them (you have to click on the Copy button or Ctrl-C or Edit, Copy - can't right-click to do it).
Paste this list back on your main sheet (or elsewhere, but then you will have to alter the formula) and you can delete the pivot table sheet.
I pasted my list in column E, and the original values are in A and B, so that is what my formula is based upon.
Next, sort your data. You can sort by just the orders or by company and then the orders. Be sure you sort Ascending so that the the lowest numbers are at the top of the list (or come first within each company listing).
Then enter this formula:
=INDEX(A:B,MATCH(E1,B:B,0),1)
This will give you the first value for the company named in E1. Drag the formula down to fill beside your full list of names.
If you want to find the max value, just sort your list again, but with the order in Descending order.

As Seen On