repeated value

C

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
 
P

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
 
S

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
 
S

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
 
C

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
 
Y

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

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top