Excel - Finding the minimum number while comparing two columns

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

ekim256

Thread Starter
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!!!
 

Keebellah

Trusted Advisor
Joined
Mar 27, 2008
Messages
6,611
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.
 

Keebellah

Trusted Advisor
Joined
Mar 27, 2008
Messages
6,611
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
 
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.
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Members online

Top