Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
duplicate occurance, assigning an ordinal value
Hi, Is there any way to not only count duplicates, i.e.
=COUNTIF(range,C2), but to assign each occurance an ordinal value? So for example, if Model1234 appears twice within "range", not only would "2" result, but that the first occurance of Model1234 would display "1" and the second occurance would display "2". Any thoughts? Thanks, Michael |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
duplicate occurance, assigning an ordinal value
=COUNTIF($A$1:A1,A1)
and copy down -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "mcheng" wrote in message ups.com... Hi, Is there any way to not only count duplicates, i.e. =COUNTIF(range,C2), but to assign each occurance an ordinal value? So for example, if Model1234 appears twice within "range", not only would "2" result, but that the first occurance of Model1234 would display "1" and the second occurance would display "2". Any thoughts? Thanks, Michael |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
duplicate occurance, assigning an ordinal value
Let's say the range is C2:C100
Try =if(countif(C$2:c$100,C2)1,countif(C$2:C2,c2),"No t a duplicated value") "mcheng" wrote: Hi, Is there any way to not only count duplicates, i.e. =COUNTIF(range,C2), but to assign each occurance an ordinal value? So for example, if Model1234 appears twice within "range", not only would "2" result, but that the first occurance of Model1234 would display "1" and the second occurance would display "2". Any thoughts? Thanks, Michael |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
duplicate occurance, assigning an ordinal value
Thanks for the response. What I'm looking for more than a flag, but
rather an assignment of a unique value of where that duplicate occurs within the series of duplicates. So for example, if Model1234 appears 8 times, the first time would assign the value "1", the second "2", etc. I found a post corresponding to this, "Nth position of occurence in a list", and am working with the formula, =small(if(range=C2,row(range),N)-cell("row",range)+1 However, am in the process of trying to understand the functions involved... Thanks again, Michael http://groups.google.com/group/micro...2778115eaf4d5d On Jun 12, 11:33 am, Duke Carey wrote: Let's say the range is C2:C100 Try =if(countif(C$2:c$100,C2)1,countif(C$2:C2,c2),"No t a duplicated value") "mcheng" wrote: Hi, Is there any way to not only count duplicates, i.e. =COUNTIF(range,C2), but to assign each occurance an ordinal value? So for example, if Model1234 appears twice within "range", not only would "2" result, but that the first occurance of Model1234 would display "1" and the second occurance would display "2". Any thoughts? Thanks, Michael- Hide quoted text - - Show quoted text - |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
duplicate occurance, assigning an ordinal value
Thanks for the response. What I'm looking for more than a flag, but
rather an assignment of a unique value of where that duplicate occurs within the series of duplicates. So for example, if Model1234 appears 8 times, the first time would assign the value "1", the second "2", etc. I found a post corresponding to this, "Nth position of occurence in a list", and am working with the formula, =small(if(range=C2,row(range),N)-cell("row",range)+1 Does this do what you are after? Assume your list is in Column A starting at A1 and the name you want to find the duplicates of is placed in C1... then place the following formula in B1 and copy down as needed. =IF(A1=$C$1,COUNTIF(A$1:A1,"="&$C$1),"") Rick |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
duplicate occurance, assigning an ordinal value
See my response.
-- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "mcheng" wrote in message ups.com... Thanks for the response. What I'm looking for more than a flag, but rather an assignment of a unique value of where that duplicate occurs within the series of duplicates. So for example, if Model1234 appears 8 times, the first time would assign the value "1", the second "2", etc. I found a post corresponding to this, "Nth position of occurence in a list", and am working with the formula, =small(if(range=C2,row(range),N)-cell("row",range)+1 However, am in the process of trying to understand the functions involved... Thanks again, Michael http://groups.google.com/group/micro...2778115eaf4d5d On Jun 12, 11:33 am, Duke Carey wrote: Let's say the range is C2:C100 Try =if(countif(C$2:c$100,C2)1,countif(C$2:C2,c2),"No t a duplicated value") "mcheng" wrote: Hi, Is there any way to not only count duplicates, i.e. =COUNTIF(range,C2), but to assign each occurance an ordinal value? So for example, if Model1234 appears twice within "range", not only would "2" result, but that the first occurance of Model1234 would display "1" and the second occurance would display "2". Any thoughts? Thanks, Michael- Hide quoted text - - Show quoted text - |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
duplicate occurance, assigning an ordinal value
Yes, Bob's formula certainly counts dup's as they appear from the top
of the list. Now it's a matter of configuring the formula and arranging the list. Thanks again. Michael On Jun 12, 5:47 pm, "Bob Phillips" wrote: See my response. -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "mcheng" wrote in message ups.com... Thanks for the response. What I'm looking for more than a flag, but rather an assignment of a unique value of where that duplicate occurs within the series of duplicates. So for example, if Model1234 appears 8 times, the first time would assign the value "1", the second "2", etc. I found a post corresponding to this, "Nth position of occurence in a list", and am working with the formula, =small(if(range=C2,row(range),N)-cell("row",range)+1 However, am in the process of trying to understand the functions involved... Thanks again, Michael http://groups.google.com/group/micro...orksheet.funct... On Jun 12, 11:33 am, Duke Carey wrote: Let's say the range is C2:C100 Try =if(countif(C$2:c$100,C2)1,countif(C$2:C2,c2),"No t a duplicated value") "mcheng" wrote: Hi, Is there any way to not only count duplicates, i.e. =COUNTIF(range,C2), but to assign each occurance an ordinal value? So for example, if Model1234 appears twice within "range", not only would "2" result, but that the first occurance of Model1234 would display "1" and the second occurance would display "2". Any thoughts? Thanks, Michael- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
duplicate occurance, assigning an ordinal value
Right now, the first dup on the list (closest to the top, or Row 1) is
assigned the smallest number, and the largest Row number is assigned the largest number. Is there a way to reverse that so that the row closes to the bottom is actually the smallest number and the row closest to the top has the highest number? Thanks again, Michael On Jun 13, 9:29 am, mcheng wrote: Yes, Bob's formula certainly counts dup's as they appear from the top of the list. Now it's a matter of configuring the formula and arranging the list. Thanks again. Michael On Jun 12, 5:47 pm, "Bob Phillips" wrote: See my response. -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "mcheng" wrote in message oups.com... Thanks for the response. What I'm looking for more than a flag, but rather an assignment of a unique value of where that duplicate occurs within the series of duplicates. So for example, if Model1234 appears 8 times, the first time would assign the value "1", the second "2", etc. I found a post corresponding to this, "Nth position of occurence in a list", and am working with the formula, =small(if(range=C2,row(range),N)-cell("row",range)+1 However, am in the process of trying to understand the functions involved... Thanks again, Michael http://groups.google.com/group/micro...orksheet.funct... On Jun 12, 11:33 am, Duke Carey wrote: Let's say the range is C2:C100 Try =if(countif(C$2:c$100,C2)1,countif(C$2:C2,c2),"No t a duplicated value") "mcheng" wrote: Hi, Is there any way to not only count duplicates, i.e. =COUNTIF(range,C2), but to assign each occurance an ordinal value? So for example, if Model1234 appears twice within "range", not only would "2" result, but that the first occurance of Model1234 would display "1" and the second occurance would display "2". Any thoughts? Thanks, Michael- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
duplicate occurance, assigning an ordinal value
Right now, the first dup on the list (closest to the top, or Row 1) is
assigned the smallest number, and the largest Row number is assigned the largest number. Is there a way to reverse that so that the row closes to the bottom is actually the smallest number and the row closest to the top has the highest number? Change Bob's formula to this =COUNTIF($A1:A$XXX,A1) where my XXX can be any row number equal to or greater than the last data row you will ever need. Rick |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
duplicate occurance, assigning an ordinal value
Thanks for the response. As I understood it, Rick, the use of XXX
referenced below doesn't do it. Here's what it looks like: http://www.editgrid.com/user/mcheng/sample Michael On Jun 13, 2:56 pm, "Rick Rothstein \(MVP - VB\)" wrote: Right now, the first dup on the list (closest to the top, or Row 1) is assigned the smallest number, and the largest Row number is assigned the largest number. Is there a way to reverse that so that the row closes to the bottom is actually the smallest number and the row closest to the top has the highest number? Change Bob's formula to this =COUNTIF($A1:A$XXX,A1) where my XXX can be any row number equal to or greater than the last data row you will ever need. Rick |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
duplicate occurance, assigning an ordinal value
Thanks for the response. As I understood it, Rick, the use of XXX
referenced below doesn't do it. Here's what it looks like: http://www.editgrid.com/user/mcheng/sample Here is what it looks like on my system... http://www.rickrothstein.com/excel_stuff/testsheet.xls seems to work as you asked for. If I did it right, you should be able to add/delete model numbers and watch the formulas work. I copied down to A20, so you have a little room to play with. Rick |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
duplicate occurance, assigning an ordinal value
The link to testsheet.xls was not accessible.
But I did find the solution by using this formula in cell A2: =COUNTIF($A$1:$A$12,A2)+1-COUNTIF($A$1:A2,A2) and filling down to the entire list. This will reverse the order of values so that the largest values are towards the top most row and the smallest values are towards the bottom. Thanks for all the help. Michael On Jun 13, 11:55 pm, "Rick Rothstein \(MVP - VB\)" wrote: Thanks for the response. As I understood it, Rick, the use of XXX referenced below doesn't do it. Here's what it looks like: http://www.editgrid.com/user/mcheng/sample Here is what it looks like on my system... http://www.rickrothstein.com/excel_stuff/testsheet.xls seems to work as you asked for. If I did it right, you should be able to add/delete model numbers and watch the formulas work. I copied down to A20, so you have a little room to play with. Rick |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
duplicate occurance, assigning an ordinal value
The link to testsheet.xls was not accessible.
Sorry, I am not sure what I did wrong. But I did find the solution by using this formula in cell A2: =COUNTIF($A$1:$A$12,A2)+1-COUNTIF($A$1:A2,A2) and filling down to the entire list. This will reverse the order of values so that the largest values are towards the top most row and the smallest values are towards the bottom. You should not need that complicated a formula. Please do an experiment for me. Start Excel and go to a new sheet. Put the following formula in B1... =COUNTIF($A1:$A$999,A1) Copy it down for 25 or so rows. Now, start typing model numbers in column A. As you repeat the model numbers, are you saying the repeat count numbering in column B is not shown from highest downward to lowest when you finish entering repeated model numbers? Rick |
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
duplicate occurance, assigning an ordinal value
Your formula gives the number of occurrences, not the ordinal value
(i.e. where it appears within the series). The formula i =COUNTIF($A1:$A$999,A1) is helpful in determining the maximum value of the series (i.e. number of times the duplicate appears). Michael On Jun 14, 10:37 am, "Rick Rothstein \(MVP - VB\)" wrote: The link to testsheet.xls was not accessible. Sorry, I am not sure what I did wrong. But I did find the solution by using this formula in cell A2: =COUNTIF($A$1:$A$12,A2)+1-COUNTIF($A$1:A2,A2) and filling down to the entire list. This will reverse the order of values so that the largest values are towards the top most row and the smallest values are towards the bottom. You should not need that complicated a formula. Please do an experiment for me. Start Excel and go to a new sheet. Put the following formula in B1... =COUNTIF($A1:$A$999,A1) Copy it down for 25 or so rows. Now, start typing model numbers in column A. As you repeat the model numbers, are you saying the repeat count numbering in column B is not shown from highest downward to lowest when you finish entering repeated model numbers? Rick |
#15
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
duplicate occurance, assigning an ordinal value
Your formula gives the number of occurrences, not the ordinal value
(i.e. where it appears within the series). The formula i =COUNTIF($A1:$A$999,A1) is helpful in determining the maximum value of the series (i.e. number of times the duplicate appears). Then I have (and I still am) completely misunderstood what you are after. In an earlier reply, you said the following... "What I'm looking for more than a flag, but rather an assignment of a unique value of where that duplicate occurs within the series of duplicates. So for example, if Model1234 appears 8 times, the first time would assign the value "1", the second "2", etc." which you then subsequently requested to be ordered in reverse. Isn't that simply numbering each occurrence with its count number, in order of the count? Unless I am completely off base here, that is what the formula above does... within each sequence of repeated model numbers, the repeated model numbers are numbered from 1 to the number of repeats for that model. This numbering of repeats is unique for each distinct set of repeated models. So, if Model1234 appears 8 times, each row with Model1234 has a number from 1 to 8 next to it. In that same list, if Model9876 is repeated 4 times, each row with Model9876 has a number for 1 to 4 next to it. If this is not what you were asking for, then I am not sure how to read what you are asking for. Can you show us a list of several model numbers, each with different number of repeats, along with the numbers you want to appear next to them? That might help clear up what you are asking for (at least, for me it will). Thank you. Rick |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count occurance of largest duplicate number in a single column ran | New Users to Excel | |||
Ordinal Numbers | Excel Worksheet Functions | |||
Format cells to display ordinal numbers ex 21st | Excel Worksheet Functions | |||
display dates with ordinal numbers in excel | Excel Discussion (Misc queries) | |||
frequency for each occurance | Excel Worksheet Functions |