|
repeated value
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 |
repeated value
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 |
repeated value
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 |
repeated value
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 |
repeated value
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 |
repeated value
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 - |
repeated value
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 |
repeated value
|
| All times are GMT +1. The time now is 07:38 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com