Match formula that pulls unique values from another column?

[alehm]

have a tough one here:

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

What I am needing is for every match of a certain carrier (column B) t
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 shippe
UPS is there a formula that exists to give me this information

 

[Guest]

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.

 

[Guest]

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)

 

[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

 

[alehm]

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?

 

[swatsp0p]

Alehm:

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

A better option may be that you use a cell reference rather than th
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

Bruc

 

[alehm]

Bruce.

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