View original (French)

Duplicate numbering/excel

Solved
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
    1. Vaucluse Contributor
      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
      1. 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
        1. 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
        3. 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
        4. gazer
          Thank you very much, that's exactly it ;)

          Have a good rest of the afternoon.
          0