Question: How do i only put duplicate cell values from a list into another new list and their adjacent values?

Answer: Here is the formula for the duplicates. (Column D)

=INDEX($A$2:$A$9, SMALL(IF(COUNTIF($A$2:$A$9, $A$2:$A$9)>1, ROW($A$2:$A$9)-MIN(ROW($A$2:$A$9))+1, ""), ROW(A1))) + Ctrl + Shift + Enter

Here is the formula for duplicates adjacent values (Column E):

=INDEX($B$2:$B$9, SMALL(IF(COUNTIF($A$2:$A$9, $A$2:$A$9)>1, ROW($A$2:$A$9)-MIN(ROW($A$2:$A$9))+1, ""), ROW(A1))) + Ctrl + Shift + Enter

Download excel sample file for this tutorial.
Extract duplicates and their adjacent values.xls
(Excel 97-2003 Workbook *.xls)

Functions in this article:

INDEX(array,row_num,[column_num])
Returns a value or reference of the cell at the intersection of a particular row and column, in a given range

SMALL() returns the k-th smallest row number in this data set.

COUNTIF(range;criteria)
Counts the number of cells within a range that meet the given condition

Share and Enjoy:
  • Share/Bookmark

Related posts:

  1. Highlight duplicates where adjacent cell value meets criteria using conditional formatting in excel part 2
  2. Extract negative values and adjacent cells in excel
  3. Extract dates and adjacent value in a range using a date critera in excel
  4. Filter unique distinct list sorted based on sum of adjacent values using array formula in excel
  5. Sum adjacent values using multiple lookup text values in a column in excel
  6. Filter unique distinct values where adjacent cells contain search string in excel
  7. Sum adjacent values from a range using multiple lookup values in excel