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

  1. 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
  2. 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
  3. 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
  4. 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
    1. Anonymous user
       
      Look at the Sum function and the If function in the help section: everything is well explained.
      0
  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
    1. Anonymous user
       
      It needs to be coupled with Sum from where sum if :)
      0
  7. gazer
     
    Thank you very much, that's exactly it ;)

    Have a good rest of the afternoon.
    0