Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Duplicate Item Numbers
In a privious post I asked the following question; Column A has hundreds of item numbers. Most are NOT duplicated, but I could have as many as 10 to 15 item numbers with duplications and each number could be duplicated 3 to 10 times or more. Question: I need a formula for column B which will return a unique number for each set of duplicates. As it turns out I gave a poor example of what I needed. ************** Apoligies to Teethless mama **************** The following is what I really need. A B 1 2 3 4 1 4 1 4 1 5 2 5 2 6 7 27 5 9 10 3 10 3 10 3 4 1 10 3 10 3 10 3 10 3 24 25 4 25 4 26 27 5 5 2 27 5 Thanks for your help |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Duplicate Item Numbers
Hi
Then you only need to modify the formula Ron gave to you originally =IF(COUNTIF($A$1:$A$24,A2)=1,"",COUNTIF($A$1:$A$24 ,A2)) -- Regards Roger Govier "lehigh46" wrote in message ... In a privious post I asked the following question; Column A has hundreds of item numbers. Most are NOT duplicated, but I could have as many as 10 to 15 item numbers with duplications and each number could be duplicated 3 to 10 times or more. Question: I need a formula for column B which will return a unique number for each set of duplicates. As it turns out I gave a poor example of what I needed. ************** Apoligies to Teethless mama **************** The following is what I really need. A B 1 2 3 4 1 4 1 4 1 5 2 5 2 6 7 27 5 9 10 3 10 3 10 3 4 1 10 3 10 3 10 3 10 3 24 25 4 25 4 26 27 5 5 2 27 5 Thanks for your help |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Duplicate Item Numbers
Assuming that A2:A28 contains the data, try the following formula that
needs to be confirmed with CONTROL+SHIFT+ENTER... B2, copied down: =IF(COUNTIF($A$2:$A$28,A2)1,SUM(IF(COUNTIF($A$2:$ A$28,$A$2:$A$28)1,IF(A 2$A$2:$A$28,1/COUNTIF($A$2:$A$28,$A$2:$A$28))))+1,"") Hope this helps! In article , lehigh46 wrote: In a privious post I asked the following question; Column A has hundreds of item numbers. Most are NOT duplicated, but I could have as many as 10 to 15 item numbers with duplications and each number could be duplicated 3 to 10 times or more. Question: I need a formula for column B which will return a unique number for each set of duplicates. As it turns out I gave a poor example of what I needed. ************** Apoligies to Teethless mama **************** The following is what I really need. A B 1 2 3 4 1 4 1 4 1 5 2 5 2 6 7 27 5 9 10 3 10 3 10 3 4 1 10 3 10 3 10 3 10 3 24 25 4 25 4 26 27 5 5 2 27 5 Thanks for your help |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Duplicate Item Numbers
Without sorting your column A values, it'll get tremendously complicated.
Here's what will work if they are sorted: In cell B2 use the formula =IF(COUNTIF($A$2:$A$28,A2)=1,"",1) In cell B3 use this formula & copy it down =IF(COUNTIF($A$2:$A$28,A3)=1,"",IF(COUNTIF(A$2:A3, A3)1,VLOOKUP(A3,A$2:B2,2,0),1+MAX(B$2:B2))) "lehigh46" wrote: In a privious post I asked the following question; Column A has hundreds of item numbers. Most are NOT duplicated, but I could have as many as 10 to 15 item numbers with duplications and each number could be duplicated 3 to 10 times or more. Question: I need a formula for column B which will return a unique number for each set of duplicates. As it turns out I gave a poor example of what I needed. ************** Apoligies to Teethless mama **************** The following is what I really need. A B 1 2 3 4 1 4 1 4 1 5 2 5 2 6 7 27 5 9 10 3 10 3 10 3 4 1 10 3 10 3 10 3 10 3 24 25 4 25 4 26 27 5 5 2 27 5 Thanks for your help |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Duplicate Item Numbers | Excel Worksheet Functions | |||
Item numbers result in item description in next field in Excel | Excel Worksheet Functions | |||
Splitting item numbers | Excel Discussion (Misc queries) | |||
item numbers in a list that update if a row is deleted | Excel Discussion (Misc queries) | |||
How do I delete a character from a list of item numbers? | Excel Worksheet Functions |