1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Excel - Finding the minimum number while comparing two columns

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

Thread Status:
Not open for further replies.
Advertisement
  1. ekim256

    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!!!
     
  2. Keebellah

    Keebellah Trusted Advisor

    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.
     
  3. Keebellah

    Keebellah Trusted Advisor

    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

    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
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 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/758382

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice