have a tough one he

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

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 he

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

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 he

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

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


--
swatsp0p


------------------------------------------------------------------------
swatsp0p's Profile: http://www.excelforum.com/member.php...o&userid=15101
View this thread: http://www.excelforum.com/showthread...hreadid=465946

swatsp0p Wrote:
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?


--
alehm
------------------------------------------------------------------------
alehm's Profile: http://www.excelforum.com/member.php...o&userid=27077
View this thread: http://www.excelforum.com/showthread...hreadid=465946

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


--
swatsp0p


------------------------------------------------------------------------
swatsp0p's Profile: http://www.excelforum.com/member.php...o&userid=15101
View this thread: http://www.excelforum.com/showthread...hreadid=465946

Bruce.

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


--
alehm
------------------------------------------------------------------------
alehm's Profile: http://www.excelforum.com/member.php...o&userid=27077
View this thread: http://www.excelforum.com/showthread...hreadid=465946