The formula in cell E2 returns duplicate values from column A but values in column C are excluded from the list.

Array formula in cell E2:

This post explains how to extract duplicate values using 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 […]

Comments(0) Filed in category: Duplicate values, Excel

You can also use more than one condition, this article shows you how:

Filter duplicate values using critera

The following worksheet allows you to search for duplicate names using a date range (cell B1 and B2) and a […]

Comments(0) Filed in category: Duplicate values, Excel

If you want a list sorted from A to Z, read this article:

Extract a list of alphabetically sorted duplicates from a column in excel

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

Comments(1) Filed in category: Duplicate values, Excel, Sort values

Filter a unique distinct list:

How to extract a unique distinct list from a column in excel

You have quite a few options to choose from if you are looking for a way to create a unique […]

Comments(149) Filed in category: Advanced filter, Excel, Unique distinct values

How to enter an array formula

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

Your formula now looks like this: {=array_formula}
Don't enter the curly brackets, they appear automatically.

Learn the basics of Excel arrays

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

Comments(2) Filed in category: Built-in features, Count values, Excel

Download excel *.xlsx file

how-to-extract-a-list-of-duplicates-from-a-columns-in-excel_version2.xlsx

The following worksheet allows you to search for duplicate names using a date range (cell B1 and B2) and a condition (cell B3).

The result is displayed in cell A6 and below.

Array formula in cell A6:

If you want a list of duplicates, sorted from A to Z, read this article:

Extract a list of alphabetically sorted duplicates from a column in excel

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

Comments(1) Filed in category: Duplicate values, Excel, Sort values

This post explains how to extract duplicate values using 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 […]

Comments(0) Filed in category: Duplicate values, Excel

Filter a unique distinct list:

How to extract a unique distinct list from a column in excel

You have quite a few options to choose from if you are looking for a way to create a unique […]

Comments(149) Filed in category: Advanced filter, Excel, Unique distinct values

Extract a list of duplicates:

Extract a list of duplicates from a column using array formula

The array formula in cell C2 extracts duplicate values from column A. Only one duplicate of each value is displayed […]

Comments(41) Filed in category: Duplicate values, Excel

How to enter an array formula

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

Your formula now looks like this: {=array_formula}
Don't enter the curly brackets, they appear automatically.

Download excel *.xlsx file

Extract duplicates using conditions.xlsx

The following image shows you data in column B and C. I want to concatenate adjacent value to size "M" into cell F4. They are "CD", "IJ" and "OP", in picture below.

I highly recommend using the TEXTJOIN function if you own Excel 2016. It is what the CONCATENATE function should have been from the beginning:

The formula in cell D3 lets you get the last value in column B, it works fine with blank cells in your list.

Formula in cell D3:

The formula is quite cpu-intensive since it is processing all cells in column B, there are more than a million cells in one column in Excel 2007 and later versions.

The array formula in cell E6 filters values in column C based on value in cell E3, the output is a sorted unique distinct list in cell E6 and below.