Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
johncassell
 
Posts: n/a
Default Count Duplicates Order numbers for one Customer


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


--
johncassell
------------------------------------------------------------------------
johncassell's Profile: http://www.excelforum.com/member.php...o&userid=25016
View this thread: http://www.excelforum.com/showthread...hreadid=511027

  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
SteveG
 
Posts: n/a
Default Count Duplicates Order numbers for one Customer


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: http://www.excelforum.com/member.php...fo&userid=7571
View this thread: http://www.excelforum.com/showthread...hreadid=511027

  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
johncassell
 
Posts: n/a
Default Count Duplicates Order numbers for one Customer


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: http://www.excelforum.com/member.php...o&userid=25016
View this thread: http://www.excelforum.com/showthread...hreadid=511027

  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Domenic
 
Posts: n/a
Default Count Duplicates Order numbers for one Customer

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 ,
johncassell
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

  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
johncassell
 
Posts: n/a
Default Count Duplicates Order numbers for one Customer


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: http://www.excelforum.com/member.php...o&userid=25016
View this thread: http://www.excelforum.com/showthread...hreadid=511027



  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Domenic
 
Posts: n/a
Default Count Duplicates Order numbers for one Customer

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 ,
johncassell
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

  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
johncassell
 
Posts: n/a
Default Count Duplicates Order numbers for one Customer


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: http://www.excelforum.com/member.php...o&userid=25016
View this thread: http://www.excelforum.com/showthread...hreadid=511027

  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Domenic
 
Posts: n/a
Default Count Duplicates Order numbers for one Customer

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 ,
johncassell
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

  #9   Report Post  
Posted to microsoft.public.excel.worksheet.functions
johncassell
 
Posts: n/a
Default Count Duplicates Order numbers for one Customer


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

Thanks again

John


--
johncassell
------------------------------------------------------------------------
johncassell's Profile: http://www.excelforum.com/member.php...o&userid=25016
View this thread: http://www.excelforum.com/showthread...hreadid=511027

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Count unique numbers for execs DanielJW Excel Discussion (Misc queries) 2 November 29th 05 11:47 AM
How do i count numbers and letters to find a total count of all Linda Excel Worksheet Functions 4 November 10th 05 05:51 PM
Number count - postcodes and need to count the numbers of tim... Mark - Aust Excel Discussion (Misc queries) 1 October 24th 05 10:00 AM
how do i count time numbers whit a negativ sign ( ex -19 hours, 25 rabbeer Excel Discussion (Misc queries) 1 June 29th 05 06:14 PM
Count and Sum Total occurrances of two specific numbers Sam via OfficeKB.com Excel Worksheet Functions 10 March 29th 05 08:13 PM


All times are GMT +1. The time now is 02:54 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"