I have 1 column (A), with 16000 rows and repeated value, i need to count
repeated value and write this in column B, for example:

A B
222 2
222
355 3
355
355
420 2
420


best regards

Try this in B1:

=IF(COUNTIF(A$1:A1,A1)=1,COUNTIF(A$1:A$16000,A1)," ")

and then copy down to B16000.

Hope this helps.

Pete

On May 28, 12:29*pm, climate
wrote:
I have 1 column (A), with 16000 rows and repeated value, i need to count
repeated value and write this in column B, for example:

A * * * * * * * *B
222 * * * * * * 2
222
355 * * * * * * 3
355
355
420 * * * * * * 2
420

best regards

Your formula returns 2 for the 3 355's

=IF(AND(A1=A2,A2<A3),COUNTIF($A$1:A2,A2),"")

in A2 and copied down will return the Op's results but if there are
duplicate number further down the list it will give the total of that
number to that point not just the latest series.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Pete_UK" wrote in message
...
Try this in B1:

=IF(COUNTIF(A$1:A1,A1)=1,COUNTIF(A$1:A$16000,A1)," ")

and then copy down to B16000.

Hope this helps.

Pete

On May 28, 12:29 pm, climate
wrote:
I have 1 column (A), with 16000 rows and repeated value, i need to count
repeated value and write this in column B, for example:

A B
222 2
222
355 3
355
355
420 2
420

best regards

Sorry Pete, I omitted your name at the beginning:

Pete,

Your formula returns 2 for the 3 355's

=IF(AND(A1=A2,A2<A3),COUNTIF($A$1:A2,A2),"")

in A2 and copied down will return the Op's results but if there are
duplicate number further down the list it will give the total of that
number to that point not just the latest series


--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Sandy Mann" wrote in message
...
Your formula returns 2 for the 3 355's

=IF(AND(A1=A2,A2<A3),COUNTIF($A$1:A2,A2),"")

in A2 and copied down will return the Op's results but if there are
duplicate number further down the list it will give the total of that
number to that point not just the latest series.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Pete_UK" wrote in message
...
Try this in B1:

=IF(COUNTIF(A$1:A1,A1)=1,COUNTIF(A$1:A$16000,A1)," ")

and then copy down to B16000.

Hope this helps.

Pete

On May 28, 12:29 pm, climate
wrote:
I have 1 column (A), with 16000 rows and repeated value, i need to count
repeated value and write this in column B, for example:

A B
222 2
222
355 3
355
355
420 2
420

best regards

Hi pete,
Your formula is right
thank you very much
Sandy formula has equal result but one cell shift down(not side by side with
first repeated value).
Thank's sandy
best regards

"Pete_UK" wrote:

Try this in B1:

=IF(COUNTIF(A$1:A1,A1)=1,COUNTIF(A$1:A$16000,A1)," ")

and then copy down to B16000.

Hope this helps.

Pete

On May 28, 12:29 pm, climate
wrote:
I have 1 column (A), with 16000 rows and repeated value, i need to count
repeated value and write this in column B, for example:

A B
222 2
222
355 3
355
355
420 2
420

best regards

I know you have a couple of answers to work with but you can also use an array:

1) type into cell b2: =FREQUENCY($a$2:$a$16000,$a$2:$a$16000)
2) then highlight cells b2 to b16000 (the cell above and the 16000 empty
rows below it
3) press f2 and ctrl-shift-enter

the advantage is, that the list doesn't have to be sorted to work.
negatives include, you can't insert any more rows (but can copy/paste values
to keep the results then do other things).


"climate" wrote:

Hi pete,
Your formula is right
thank you very much
Sandy formula has equal result but one cell shift down(not side by side with
first repeated value).
Thank's sandy
best regards

"Pete_UK" wrote:

Try this in B1:

=IF(COUNTIF(A$1:A1,A1)=1,COUNTIF(A$1:A$16000,A1)," ")

and then copy down to B16000.

Hope this helps.

Pete

On May 28, 12:29 pm, climate
wrote:
I have 1 column (A), with 16000 rows and repeated value, i need to count
repeated value and write this in column B, for example:

A B
222 2
222
355 3
355
355
420 2
420

best regards

Sandy,

works okay for me. Did you put it in B2?

Pete

On May 28, 1:20*pm, "Sandy Mann" wrote:
Sorry Pete, I omitted your name at the beginning:

Pete,

Your formula returns 2 for the 3 355's

=IF(AND(A1=A2,A2<A3),COUNTIF($A$1:A2,A2),"")

in A2 and copied down will return the Op's results but if there are
duplicate number further down the list it *will give the total of that
number to that point not just the latest series

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk

"Sandy Mann" wrote in message

...



Your formula returns 2 for the 3 355's

=IF(AND(A1=A2,A2<A3),COUNTIF($A$1:A2,A2),"")

in A2 and copied down will return the Op's results but if there are
duplicate number further down the list it *will give the total of that
number to that point not just the latest series.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk

"Pete_UK" wrote in message
...
Try this in B1:

=IF(COUNTIF(A$1:A1,A1)=1,COUNTIF(A$1:A$16000,A1)," ")

and then copy down to B16000.

Hope this helps.

Pete

On May 28, 12:29 pm, climate
wrote:
I have 1 column (A), with 16000 rows and repeated value, i need to count
repeated value and write this in column B, for example:

A B
222 2
222
355 3
355
355
420 2
420

best regards- Hide quoted text -

- Show quoted text -

You're welcome - thanks for feeding back.

Pete

On May 28, 1:53*pm, climate wrote:
Hi pete,
Your formula is right
thank you very much
Sandy formula has equal result but one cell shift down(not side by side with
first repeated value).
Thank's sandy
best regards

Hi Pete,

Yes you are quite correct that is exactly what I did do. My apologies.

--

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Pete_UK" wrote in message
...
Sandy,

works okay for me. Did you put it in B2?

Pete

On May 28, 1:20 pm, "Sandy Mann" wrote:
Sorry Pete, I omitted your name at the beginning:

Pete,

Your formula returns 2 for the 3 355's

=IF(AND(A1=A2,A2<A3),COUNTIF($A$1:A2,A2),"")

in A2 and copied down will return the Op's results but if there are
duplicate number further down the list it will give the total of that
number to that point not just the latest series

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk

"Sandy Mann" wrote in message

...



Your formula returns 2 for the 3 355's

=IF(AND(A1=A2,A2<A3),COUNTIF($A$1:A2,A2),"")

in A2 and copied down will return the Op's results but if there are
duplicate number further down the list it will give the total of that
number to that point not just the latest series.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk

"Pete_UK" wrote in message
...
Try this in B1:

=IF(COUNTIF(A$1:A1,A1)=1,COUNTIF(A$1:A$16000,A1)," ")

and then copy down to B16000.

Hope this helps.

Pete

On May 28, 12:29 pm, climate
wrote:
I have 1 column (A), with 16000 rows and repeated value, i need to
count
repeated value and write this in column B, for example:

A B
222 2
222
355 3
355
355
420 2
420

best regards- Hide quoted text -

- Show quoted text -