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

INDEX function explained

Fetch a value in a data set based on coordinates.

IF function explained

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

Learn to use the COUNTIF function

Counts the number of times a value exists in a cell range.

ROW function explained

ROW(reference) Returns the row number of a reference. You can enter a reference to a single cell or a cell range. […]