Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count unique numbers for execs | Excel Discussion (Misc queries) | |||
How do i count numbers and letters to find a total count of all | Excel Worksheet Functions | |||
Number count - postcodes and need to count the numbers of tim... | Excel Discussion (Misc queries) | |||
how do i count time numbers whit a negativ sign ( ex -19 hours, 25 | Excel Discussion (Misc queries) | |||
Count and Sum Total occurrances of two specific numbers | Excel Worksheet Functions |