software

Hello, can anyone tell me the formula for this..
Range A:A contains job numbers.
Range B:B contains the customer for that job number.
Ranbe C:C contains the order number for that job number.

I need a formula which says quot;Tell me how many order numbers for
Customer X are duplicatesquot;

Basically Customer X can't have two of the same order number and I need
to know how many there are.

I have the following array formula to identify duplicate job numbers if
that helps..

=COUNTA($A$9:$A$4998)-SUM(IF(FREQUENCY(IF(LEN($A$9:$A$4998)gt;0,MATCH($A$9 :$A$4998,$A$9:$A$4998,0),quot;quot;),
IF(LEN($A$9:$A$4998)gt;0,MATCH($A$9:$A$4998,$A$9:$A$ 4998,0),quot;quot;))gt;0,1))

Any help would be greatly appreciated.

Thanks

John--
johncassell
------------------------------------------------------------------------
johncassell's Profile: www.excelforum.com/member.php...oamp;userid=25016
View this thread: www.excelforum.com/showthread...hreadid=511027
John,

You could easily get the info using a Pivot Table rather than fusing
formulas.

Select the Array of data including your Headers. Click on Data Menu,
PivotTable and PivotChart Report. The PivotTable Wizard appears, click
Next, Next. Select either Existing Worksheet or New worksheet. If
existing, select the cell where you want the PT to be. Select Layout,
Drag the Customer # and Order # headings to the Row area. Drag the
Order # heading to the Data area. Double click on it to change to
Count if it is not already Count. Click Ok and Finish. Format the
table as needed. This will show you each customer number, their
corresponding order numbers and how many of each order number. If it
is greater than 1, you have a duplicate.

Does that help?

Steve--
SteveG
------------------------------------------------------------------------
SteveG's Profile: www.excelforum.com/member.php...foamp;userid=7571
View this thread: www.excelforum.com/showthread...hreadid=511027
Thanks for that Steve but I am trying to get this formula to eventually
work up to VB code which will check if that customer has any duplicate
order numbers before running more complicated code.

I don't think a pivot table will help in this situation.

Thanks

John--
johncassell
------------------------------------------------------------------------
johncassell's Profile: www.excelforum.com/member.php...oamp;userid=25016
View this thread: www.excelforum.com/showthread...hreadid=511027Try...

=SUMPRODUCT(--(B2:B10=E2),--(MATCH(B2:B10amp;quot;#quot;amp;C2:C10,B2:B10amp;quot;#quot;amp;C2:C10,0)
lt;gt;ROW(B2:B10)-ROW(B2) 1))

....where E2 contains the customer of interest.

Hope this helps!

In article gt;,
johncassell gt;
wrote:

gt; Hello, can anyone tell me the formula for this..
gt; Range A:A contains job numbers.
gt; Range B:B contains the customer for that job number.
gt; Ranbe C:C contains the order number for that job number.
gt;
gt; I need a formula which says quot;Tell me how many order numbers for
gt; Customer X are duplicatesquot;
gt;
gt; Basically Customer X can't have two of the same order number and I need
gt; to know how many there are.
gt;
gt; I have the following array formula to identify duplicate job numbers if
gt; that helps..
gt;
gt; =COUNTA($A$9:$A$4998)-SUM(IF(FREQUENCY(IF(LEN($A$9:$A$4998)gt;0,MATCH($A$9 :$A$49
gt; 98,$A$9:$A$4998,0),quot;quot;),
gt; IF(LEN($A$9:$A$4998)gt;0,MATCH($A$9:$A$4998,$A$9:$A$ 4998,0),quot;quot;))gt;0,1))
gt;
gt; Any help would be greatly appreciated.
gt;
gt; Thanks
gt;
gt; John


Thanks Domenic, I appreciate the reply but the doesnt seem to be
working.

Heres an example:

Job Number Customer Order Ref/Number
1 United 1
2 Hoyer jimmy
3 United 1
4 Hoyer dave jones
5 Deltank 75858
6 United 2
7 United 3

The result of the formula should be 1, i.e. there is 1 order number for
United that has been duplicated and if I changed the order number for
job 7 to 2 the result would be 2.

Thanks again for the replies, this is really baffling me!!!

John--
johncassell
------------------------------------------------------------------------
johncassell's Profile: www.excelforum.com/member.php...oamp;userid=25016
View this thread: www.excelforum.com/showthread...hreadid=511027If I understand you correctly, you'd like to determine the number of
duplicate order numbers for a particular customer.

So, assuming that A2:C8 contains your data, enter the customer of
interest in, let's say, E2. For this example, we'll enter United. Then
use the following formula...

=IF(E2lt;gt;quot;quot;,SUMPRODUCT(--(B2:B100=E2),--(MATCH(C2:C100amp;quot;quot;,C2:C100amp;quot;quot;,0)lt;gt;R
OW(B2:B100)-ROW(B2) 1)),quot;quot;)

According to your table, the formula will return 1. And if we change
Job Number 7 to 3, the formula will return 2.

Does this help?

In article gt;,
johncassell gt;
wrote:

gt; Thanks Domenic, I appreciate the reply but the doesnt seem to be
gt; working.
gt;
gt; Heres an example:
gt;
gt; Job Number Customer Order Ref/Number
gt; 1 United 1
gt; 2 Hoyer jimmy
gt; 3 United 1
gt; 4 Hoyer dave jones
gt; 5 Deltank 75858
gt; 6 United 2
gt; 7 United 3
gt;
gt; The result of the formula should be 1, i.e. there is 1 order number for
gt; United that has been duplicated and if I changed the order number for
gt; job 7 to 2 the result would be 2.
gt;
gt; Thanks again for the replies, this is really baffling me!!!
gt;
gt; John


Thanks for that Domenic that was exactly what I was looking for, apart
from one thing which i forgot to mention, sorry! - There will be a lot
of jobs which don't have an order number yet (i.e blank cells) and I
would like to ignore these ones.

Your formula was far too clever for me so I couldn't work out how to
exclude blanks cells, is this easy to work into the formula?

Thanks for all your help

John--
johncassell
------------------------------------------------------------------------
johncassell's Profile: www.excelforum.com/member.php...oamp;userid=25016
View this thread: www.excelforum.com/showthread...hreadid=511027Sorry John! I should have anticipated such a scenario. Try the
following formula instead...

=IF(E2lt;gt;quot;quot;,SUMPRODUCT(--(B2:B100=E2),--(C2:C100lt;gt;quot;quot;),--(MATCH(C2:C100amp;quot;quot;,
C2:C100amp;quot;quot;,0)lt;gt;ROW(B2:B100)-ROW(B2) 1)),quot;quot;)

Hope this helps!

In article gt;,
johncassell gt;
wrote:

gt; Thanks for that Domenic that was exactly what I was looking for, apart
gt; from one thing which i forgot to mention, sorry! - There will be a lot
gt; of jobs which don't have an order number yet (i.e blank cells) and I
gt; would like to ignore these ones.
gt;
gt; Your formula was far too clever for me so I couldn't work out how to
gt; exclude blanks cells, is this easy to work into the formula?
gt;
gt; Thanks for all your help
gt;
gt; John


Yep thats everything I need, thanks for all your help Domenic. I really
appreciated it mate.

Thanks again

John--
johncassell
------------------------------------------------------------------------
johncassell's Profile: www.excelforum.com/member.php...oamp;userid=25016
View this thread: www.excelforum.com/showthread...hreadid=511027