Count Duplicates Order numbers for one Customer

[johncassell]

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 "Tell me how many order numbers for
Customer X are duplicates"

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)>0,MATCH($A$9:$A$4998,$A$9:$A$4998,0),""),
IF(LEN($A$9:$A$4998)>0,MATCH($A$9:$A$4998,$A$9:$A$4998,0),""))>0,1))

Any help would be greatly appreciated.

Thanks

John

 

[SteveG]

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

 

[johncassell]

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

 

[Domenic]

Try...

=SUMPRODUCT(--(B2:B10=E2),--(MATCH(B2:B10&"#"&C2:C10,B2:B10&"#"&C2:C10,0)
<>ROW(B2:B10)-ROW(B2)+1))

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

Hope this helps!

 

[johncassell]

Thanks Domenic, I appreciate the reply but the doesnt seem to b
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 fo
United that has been duplicated and if I changed the order number fo
job 7 to 2 the result would be 2.

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

Joh

 

[Domenic]

If 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(E2<>"",SUMPRODUCT(--(B2:B100=E2),--(MATCH(C2:C100&"",C2:C100&"",0)<>R
OW(B2:B100)-ROW(B2)+1)),"")

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?

 

[johncassell]

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

 

[Domenic]

Sorry John! I should have anticipated such a scenario. Try the
following formula instead...

=IF(E2<>"",SUMPRODUCT(--(B2:B100=E2),--(C2:C100<>""),--(MATCH(C2:C100&"",
C2:C100&"",0)<>ROW(B2:B100)-ROW(B2)+1)),"")

Hope this helps!

 

[johncassell]

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

Thanks again

John