duplicate occurance, assigning an ordinal value

M

mcheng

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
 
B

Bob Phillips

=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)
 
D

Duke Carey

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")
 
M

mcheng

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/micr...occurrance+list+excel&rnum=3#ae2778115eaf4d5d
 
R

Rick Rothstein \(MVP - VB\)

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
Click to expand...

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
 
B

Bob Phillips

See my response.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
M

mcheng

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
 
M

mcheng

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
 
R

Rick Rothstein \(MVP - VB\)

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?
Click to expand...

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
 
R

Rick Rothstein \(MVP - VB\)

M

mcheng

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
 
R

Rick Rothstein \(MVP - VB\)

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.
Click to expand...

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
 
M

mcheng

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)
Click to expand...
is helpful in determining the maximum value of the series (i.e. number
of times the duplicate appears).
Michael
 
R

Rick Rothstein \(MVP - VB\)

Your formula gives the number of occurrences, not the ordinal value
(i.e. where it appears within the series). The formula i
is helpful in determining the maximum value of the series (i.e. number
of times the duplicate appears).
Click to expand...

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
 
Top