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?


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


  1. Formula-copy cell C2 down through cell C6


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


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


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


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.