Count Duplicates Order numbers for one Customer

**johncassell** · 02-10-2006, 09:45 AM

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** · 02-10-2006, 10:51 AM

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** · 02-10-2006, 10:58 AM

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 · 02-10-2006, 04:40 PM

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!

In article <[email protected]>,
johncassell <[email protected]>
wrote:

> 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$49
> 98,$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

**johncassell** · 02-11-2006, 06:34 PM

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

Domenic · 02-12-2006, 10:40 AM

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?

In article <[email protected]>,
johncassell <[email protected]>
wrote:

> 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** · 02-12-2006, 03:08 PM

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 · 02-12-2006, 03:50 PM

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!

In article <[email protected]>,
johncassell <[email protected]>
wrote:

> 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