Search code examples
excelexcel-formulaexcel-2007countif

Copy duplicate values from one column to a separate sheet

I have a list of names in one column in sheetA. I want to copy the duplicate names from sheet A to sheet B and also provide a count of those duplicate names beside each copied name in sheet B.

Solution

  • I’m guessing it is ‘manually’ so would suggest a helper column in sheetA with =COUNTIF(A:A,A1) copied down (assuming your list of names starts in A1. For each row this should count the number of rows that contain that row’s name. Copy ColumnA:B into your sheetB with Paste Special Values, then use Data > Data Tools – Remove Duplicates and filter and delete all rows with 1 in the count column.