Article updated on August 24, 2017

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)

Array formula in cell D2:

=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)))

The following article demonstrates how to filter duplicates based on a condition:

Create a list of duplicates where adjacent cell value meets a condition

Question: How do I filter duplicates with a condition? Answer: Column B contains category and column C contains Items. Only […]

Here is the formula for duplicates adjacent values, array formula in cell E2:

=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)))

This post shows you how to filter duplicates sorted from A to Z:

Extract a list of alphabetically sorted duplicates from a column

Question: How do I create a new list of alphabetically sorted duplicates using excel array formula? Answer: Excel array formula […]

How to enter array formula

  1. Copy (Ctrl + c) above formula
  2. Double click on cell D2
  3. Paste formula to cell D2
  4. Press and hold CTRL + SHIFT simultaneously
  5. Press Enter once
  6. Release all keys

The formula now looks like this: {=arrayformula}

Don't enter the curly brackets yourself, they appear automatically.

Learn the basics of Excel arrays

Array formulas allows you to do advanced calculations not possible with regular formulas.

Download excel sample file for this tutorial

Extract duplicates and their adjacent values.xls
(Excel 97-2003 Workbook *.xls)

Functions in this article

How to use the INDEX function

Gets a value in a specific cell range based on a row and column number.

How to use the IF function

Checks if a logical expression is met. Returns a specific value if TRUE and another specific value if FALSE.

How to use the COUNTIF function

Counts the number of cells that meet a specific condition.

How to use the ROW function

The ROW function calculates the row number of a cell reference. Excel Function Syntax ROW(reference) Arguments reference Optional. A reference […]