Excel – Find all duplicates except the first one, then get values from a different column

duplicatemicrosoft excelmicrosoft-excel-2010

A     B                    

X1    16      
X2    17    
X2    18
X2    19 
X3    21

I have two columns as above.

If there are duplicates in Column A (here the duplicate value is "X2"), then I need to insert in another sheet:

  1. value "18" – i.e. second value of duplicates
  2. value "19" – i.e. subsequent value(s)

How can I find duplicates in column A, and then for all duplicates except the first one from each group, get the values from a different column?

I have tried with many IF, FIND formulas, but nothing seems to work.

Any suggestions?

Best Answer

Let's say your values are in cells A2:B6. You can accomplish this by performing the following steps:

  1. Paste the following formula into cell C2 (and also provide a column header, if desired). This formula will return TRUE for all duplicates that are not the first instance, and it will return FALSE otherwise: =COUNTIF($A$2:$A2, A2)>1

    screenshot1


  1. Formula-copy cell C2 down through cell C6

    screenshot2


  1. Select cells A1:C6, click the "Data" ribbon, then click the "Filter" icon

    screenshot3


  1. Click the filter dropdown that now exists in cell C1, and de-select the FALSE checkbox

    screenshot4


  1. Select the values in column B that are still visible, and copy/paste to the new sheet

    screenshot5

Related Question