+ Reply to Thread
Results 1 to 7 of 7

Match formula that pulls unique values from another column?

  1. #1
    Registered User
    Join Date
    09-08-2005
    Posts
    3

    Match formula that pulls unique values from another column?

    have a tough one here:

    I have a report that shows shipped orders (column A) and the carrier used for that order (column B). Since the order number in Column A is repeated for every item shipped, Column A is not reliable for unique order numbers. Also different orders have different carriers in Column B.

    What I am needing is for every match of a certain carrier (column B) to pull back unique order numbers in Column A.


    Here is a visual example if a better explination is needed:

    Order Carrier
    1 UPS
    1 UPS
    2 FedEx
    3 UPS
    3 UPS
    3 UPS
    4 UPS
    4 UPS
    5 FedEx

    From the list above, if I wanted to know how many unique orders shipped UPS is there a formula that exists to give me this information?

  2. #2
    Forum Expert swatsp0p's Avatar
    Join Date
    10-07-2004
    Location
    Kentucky, USA
    MS-Off Ver
    Excel 2010
    Posts
    1,545
    Hi, Alehm: Not too tough... try this and adjust listed ranges to meet your needs:

    =SUMPRODUCT((A2:A100<>"")*(B2:B100="UPS")/COUNTIF(A2:A100,A2:A100&""))

    blank lines will be ignored.

    In your example, this will return 3

    HTH

    Bruce
    Last edited by swatsp0p; 09-08-2005 at 12:56 PM.
    Bruce
    The older I get, the better I used to be.
    USA

  3. #3
    paige
    Guest

    RE: Match formula that pulls unique values from another column?

    you should be able to do the following:

    data/filter/advanced filter function....do this for column a, then all you
    need to do is a vlookup of the carriers back into your unique column.



    "alehm" wrote:

    >
    > have a tough one here:
    >
    > I have a report that shows shipped orders (column A) and the carrier
    > used for that order (column B). Since the order number in Column A is
    > repeated for every item shipped, Column A is not reliable for unique
    > order numbers. Also different orders have different carriers in Column
    > B.
    >
    > What I am needing is for every match of a certain carrier (column B) to
    > pull back unique order numbers in Column A.
    >
    >
    > Here is a visual example if a better explination is needed:
    >
    > Order Carrier
    > 1 UPS
    > 1 UPS
    > 2 FedEx
    > 3 UPS
    > 3 UPS
    > 3 UPS
    > 4 UPS
    > 4 UPS
    > 5 FedEx
    >
    > From the list above, if I wanted to know how many unique orders shipped
    > UPS is there a formula that exists to give me this information?
    >
    >
    > --
    > alehm
    > ------------------------------------------------------------------------
    > alehm's Profile: http://www.excelforum.com/member.php...o&userid=27077
    > View this thread: http://www.excelforum.com/showthread...hreadid=465946
    >
    >


  4. #4
    paige
    Guest

    RE: Match formula that pulls unique values from another column?

    or....

    just make a list of your unique carries in row A. then so a sumif formula
    for every time you get a number of orders for that carrier......

    =SUMIF(range,criteria,sumrange)




    "paige" wrote:

    > you should be able to do the following:
    >
    > data/filter/advanced filter function....do this for column a, then all you
    > need to do is a vlookup of the carriers back into your unique column.
    >
    >
    >
    > "alehm" wrote:
    >
    > >
    > > have a tough one here:
    > >
    > > I have a report that shows shipped orders (column A) and the carrier
    > > used for that order (column B). Since the order number in Column A is
    > > repeated for every item shipped, Column A is not reliable for unique
    > > order numbers. Also different orders have different carriers in Column
    > > B.
    > >
    > > What I am needing is for every match of a certain carrier (column B) to
    > > pull back unique order numbers in Column A.
    > >
    > >
    > > Here is a visual example if a better explination is needed:
    > >
    > > Order Carrier
    > > 1 UPS
    > > 1 UPS
    > > 2 FedEx
    > > 3 UPS
    > > 3 UPS
    > > 3 UPS
    > > 4 UPS
    > > 4 UPS
    > > 5 FedEx
    > >
    > > From the list above, if I wanted to know how many unique orders shipped
    > > UPS is there a formula that exists to give me this information?
    > >
    > >
    > > --
    > > alehm
    > > ------------------------------------------------------------------------
    > > alehm's Profile: http://www.excelforum.com/member.php...o&userid=27077
    > > View this thread: http://www.excelforum.com/showthread...hreadid=465946
    > >
    > >


  5. #5
    Registered User
    Join Date
    09-08-2005
    Posts
    3
    Quote Originally Posted by swatsp0p
    Hi, Alehm: Not too tough... try this and adjust listed ranges to meet your needs:

    =SUMPRODUCT((A2:A100<>"")*(B2:B100="UPS")/COUNTIF(A2:A100,A2:A100&""))

    blank lines will be ignored.

    In your example, this will return 3

    HTH

    Bruce
    swatp0p:

    This is exactly what I need except there is one problem I am having.

    When presenting my example I used "UPS" as a carrier when in fact the carrier is actually a number in my report "22709838". When I use your formula and change UPS in your example to the actual carrier I am matching against I get zero records. However, if I manually change that number to text "UPS" on the report and formula then it works.

    Is there a reason why the formula will work with text and not numeric characters?

  6. #6
    Forum Expert swatsp0p's Avatar
    Join Date
    10-07-2004
    Location
    Kentucky, USA
    MS-Off Ver
    Excel 2010
    Posts
    1,545
    Alehm:

    I can only guess that your 22709838 carrier number is entered as text. Try enclosing the number in quotation marks ("22709838") in your formula. By the way, if you are entering the quotes, then your number is NOT text, therefore, remove the quotations marks.

    A better option may be that you use a cell reference rather than the exact entry. e.g.
    =SUMPRODUCT((A2:A13<>"")*(B2:B13=B2)/COUNTIF(A2:A13,A2:A13&""))

    where B2 holds the carrier information.

    Either should solve your problem.

    Good Luck

    Bruce

  7. #7
    Registered User
    Join Date
    09-08-2005
    Posts
    3

    Thumbs up

    Bruce.

    You're that man. Thanks for the kind help. And thanks to everyone else who chipped in as well. It is much appreciated.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1