 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Worksheet Functions / June 2007duplicate 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 =COUNTIF($A$1:A1,A1) and copy down  SignatureHTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) > Hi, Is there any way to not only count duplicates, i.e. > =COUNTIF(range,C2), but to assign each occurance an ordinal value? So [quoted text clipped - 6 lines] > Thanks, > Michael Let's say the range is C2:C100 Try =if(countif(C$2:c$100,C2)>1,countif(C$2:C2,c2),"Not a duplicated 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 [quoted text clipped - 6 lines] > Thanks, > Michael 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/microsoft.public.excel.worksheet.functions/browse _thread/thread/6d695d2ddcea71cf/ae2778115eaf4d5d?lnk=st&q=series+occurrance+list +excel&rnum=3#ae2778115eaf4d5d On Jun 12, 11:33 am, Duke Carey <DukeCa...@discussions.microsoft.com> wrote: > Let's say the range is C2:C100 > [quoted text clipped - 14 lines] > > - Show quoted text - > 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 [quoted text clipped - 3 lines] > 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 See my response. SignatureHTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) > 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 [quoted text clipped - 32 lines] >> >> - Show quoted text - 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 > See my response. > [quoted text clipped - 43 lines] > > - Show quoted text - 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 > 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 [quoted text clipped - 55 lines] > > - Show quoted text - > 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 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\)" <rickNOSPAMn...@NOSPAMcomcast.net> 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 [quoted text clipped - 10 lines] > > Rick > 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 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\)" <rickNOSPAMn...@NOSPAMcomcast.net> wrote: > > Thanks for the response. As I understood it, Rick, the use of XXX > > referenced below doesn't do it. [quoted text clipped - 10 lines] > > Rick > 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: > [quoted text clipped - 5 lines] > 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 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\)" <rickNOSPAMn...@NOSPAMcomcast.net> wrote: > > The link to testsheet.xls was not accessible. > [quoted text clipped - 21 lines] > > Rick > 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 to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.