duplicate occurance, assigning an ordinal value

[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

 

[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)

 

[Guest]

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

 

[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

 

[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

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

 

[Bob Phillips]

See my response.

--
HTH

Bob

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

 

[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

 

[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

 

[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?

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

 

[mcheng]

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

 

[Rick Rothstein \(MVP - VB\)]

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

 

[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

 

[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.

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

 

[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)

is helpful in determining the maximum value of the series (i.e. number
of times the duplicate appears).
Michael

 

[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).

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