repeated value

[climate]

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

 

[Pete_UK]

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

 

[Sandy Mann]

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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk


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

 

[Sandy Mann]

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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[climate]

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

 

[YouBetcha]

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

 

[Pete_UK]

Sandy,

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

Pete

 

[Pete_UK]

You're welcome - thanks for feeding back.

Pete

 

[Sandy Mann]

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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk


Sandy,

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

Pete