View original (French)

Remove duplicates without shifting cells

Solved
info1010 Posted messages 11 Status Member -  
info1010 Posted messages 11 Status Member -
Hello,

In Excel, I have a column of data with duplicates that I need to remove.
The "Remove Duplicates" function in the "Data Tools" tab under the "Data" menu does this job very well, except it shifts the cells up. I would like the cells not to be shifted.

Is there a solution?

6 answers

  1.
    Vaucluse Posted messages 27336 Registration date   Status Contributor Last intervention   6 453
     
    Hello
    example to adjust:
    your list starting from A1 in column A
    in column B, the formula in B1 to drag down:
    =IF(COUNTIF($A$1:A1,A1)>1,"",A1)
    by locking the first A1 of the range limit, not the second...
    ... it will display each name from column A only once

    All that will be left is to do a copy / paste special value where you want the list.

    Note for your information if needed:
    to enter the formula over 20000 rows:
    if your list in A does not have any empty cells and if you place the formula in an adjacent column

    enter the formula in B1

    click twice on the small cross at the bottom right of the cell

    otherwise: in the window to the left of the column headers, enter:
    B1:B20000
    place the formula corresponding to B1 and enter while holding down the Ctrl key.

    sincerely

    --
    Let's ask ourselves if we are the only ones who understand what we are explaining?
    9