## Extract duplicates and their adjacent values

*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:**

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:

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

- Copy (Ctrl + c) above formula
- Double click on cell D2
- Paste formula to cell D2
- Press and hold CTRL + SHIFT simultaneously
- Press Enter once
- 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

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

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.

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

Extract a list of duplicates from three columns combined using array formula in excel

Question: I have three ranges or lists and I need to extract duplicates from those ranges combined using excel array […]Filter unique distinct and duplicate values from a large data set in excel 2007

In this post I am going to describe how to filter duplicate and unique distinct values from a really large […]### 3 Responses to “Extract duplicates and their adjacent values”

### Leave a Reply

**How to add a formula to your comment:**

<code>your formula</code>

Remember to convert less than and larger than signs to html character entities before you post your comment.

**How to add VBA code to your comment:**

[vb 1="vbnet" language=","]

VBA code

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org

Add picture link to comment.

**Contact Oscar**

You can contact me through this webpage

Hi, This doesnt seem to work when I am trying to use it. It only parses one cell not a full lise. I roughly have to look for duplicates in a list of 2916. The adjacent cells just have letters and numbers. I want to do the samething here you have. I want to take all my duplicates with the adjacent data and place them in a seperate column. (originally I just wanted to take one column of one spreadsheet and another column of the other and create a list in another excel sheet or not a column in the other but this seemed hard to fine). Any help would be appreciated.

chad king,

You are right! I have changed the array formula and uploaded a tutorial file.

Thanks for bringing this to my attention!

https://www.get-digital-help.com/2009/01/12/show-duplicates-and-their-adjacent-values/

I am trying to use the above. My usages is similar...but in my usage the data in column A is auto populated with data from another sheet by using formulas. That being said, The table doesn't' work. If I remove the formulas and fill in column A manually, then it works.

How can I make this work with formulas in column A.

BTW... In my usage, most of the data in column A is blank and the only thing I need to look for is the letter D.