Remove duplicates without shifting cells
Solved
info1010
Posted messages
11
Status
Membre
-
info1010 Posted messages 11 Status Membre -
info1010 Posted messages 11 Status Membre -
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?
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 réponses
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?
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?