View original (French)

Duplicate numbering/excel

Solved
gazer -  
 gazer -
Hello,

here is my case

in column "A" I have a list of first names (thousands) that for some repeat from 1 to more than 10 times

is there a formula that says:

if there are for example 5 duplicates of the same first name in column "A" then it will display 5 in column "B"?

Thank you for your help

Configuration: Windows XP / Firefox 3.5.16

6 answers

Answer 1 / 6
Anonymous user
 
Hello

In column B, you put
=COUNTIF(A:A,A2) and drag it down the length of the field

Is it better?

--
Argitxu
3
Answer 2 / 6
Vaucluse Posted messages 27336 Registration date   Status Contributor Last intervention   6 453
 
Hello
in column B, you have two options:
starting in B1 if the first is in A1

=COUNTIF($A$1:A1,A1)
by locking the first A1 with $A$1, it will give you the number of times the code A1 (and subsequent ones) appears in the field above the row, including the row where the formula is located
you can also write:
=IF(COUNTIF($A$1:A1,A1)=1,"","duplicate n° " & COUNTIF($A$1:A1,A1))

=COUNTIF(A:A,A1)
will give you the total number of values of A1 (and subsequent ones) in column A for each row
best regards

Let’s ask ourselves if we are the only ones who understand what is being explained?
1
Answer 3 / 6
Anonymous user
 
sum if
--
Fan of "Handbag" the croc from the animated series "the pirate family"
All roads lead to Drăgășani, RO !!!
0
Answer 4 / 6
gazer
 
I'm not a pro at Excel; settling this little detail for work would really help me out.

What do I do with "if"?
0
Anonymous user
 
Look at the Sum function and the If function in the help section: everything is well explained.
0
Answer 5 / 6
gazer
 
=IF(F10="","",IF(COUNTIF(F:F,F10)>1,"duplicate"))

I have this formula but it doesn't differentiate if the duplicate appears 2 times or 10 times.
0
Anonymous user
 
It needs to be coupled with Sum from where sum if :)
0
Answer 6 / 6
gazer
 
Thank you very much, that's exactly it ;)

Have a good rest of the afternoon.
0