## Extract a unique distinct list from three columns in excel

**Question:** How do I extract a unique distinct list from three ranges or lists? The ranges are not necessarily adjacent or the exact same length.

**Answer:**

- Extract a unique distinct list from three columns
- Extract a unique distinct list from three columns with possible blanks

### Extract a unique distinct list from three columns

**Array formula in D2:**

### Extract a unique distinct list from three columns with possible blanks

**Array formula in D2:**

### How to enter an array formula

- Select cell D2
- Click in formula bar
- Paste array formula to formula bar
- Press and hold CTRL + SHIFT
- Press ENTER

### How to copy array formula

Copy cell D2 and paste it down as far as needed.

### How to customize the array formula to your excel spreadsheet

Change named ranges. If your duplicates list starts at, for example, F3. Change D1:$D$1 in the above formulas to F2:$F$2.

### Download excel sample file for this tutorial

how-to-extract-a-unique-list-from-three-columns-in-excelv2.xlsx

(Excel 2007 Workbook *.xlsx)

**Functions in this article:**

**MATCH(**lookup_value;lookup_array; [match_type]

Returns the relative position of an item in an array that matches a specified value

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

**COUNTIF(**range,criteria**)**

Counts the number of cells within a range that meet the given condition

**IFERROR(**value;value_if_error**)**

Returns value_if_error if expression is an error and the value of the expression itself otherwise

### 31 Responses to “Extract a unique distinct list from three columns in excel”

### Leave a Reply

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

<code>your formula</code>

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

hello,

i´m looking for a solution for this problem (with 7 cols) for excel 2003. One problem: the lists in the columns sometimes have empty cells in between.

Hope you can help...

Thank you

Harold

Hello Harold!

I think that would require vba. Post your question in a forum like http://www.excelforum.com/. I am sure they can help you.

Thank you for your comment!

/Oscar

I need to improve my vba skills...

Harold,

Maybe this blog post can help you?

http://www.get-digital-help.com/2009/09/16/extract-a-unique-distinct-list-sorted-alphabetically-removing-blanks-from-a-range-in-excel/

Dear, Oscar,

I have a two ranges/lists. How to extract the same values from other range contained in two ranges (without VBA)?

Can you help?

Rita,

unique-distinct-and-common-values-from-two-ranges.xls

That is what I wanted.

Thanks for your quick reply, Oscar!

Rita.

Oscar,

Could you modify you formula to use it with criterion?

e.g. - criterion for GG and II from your reply is QWERTY in A:A column, bun in B:B are other criteria in coincidence with one II.

Can you help?

Rita,

I am not sure I understand. Can you provide an example and desired outcome?

UNIQUE DISTINCT AND COMMON VALUES FROM TWO RANGES USING CRITERION

CRITERIA List1 CRITERIA List2 Common values with criterion:

for for

list 1 list2

other AA QWERTY GG GG

other BB other HH JJ

other CC other II #error!

QWERTY GG QWERTY JJ

other EE other KK

other FF other LL

other GG other MM

other HH other YY

QWERTY II other OO

QWERTY JJ other PP

Thank's.

Ohh, sorry!

Another stucture when posting.

Rita,

See attached file:

unique-distinct-and-common-values-from-two-ranges.xlsx

Not exactly, Oscar.

Because the G:G range didn't exclude HH value (HH="other").

What I want it so that the G:G range only have a values with QWERTY criterion.

Thanks anyway.

But you're still help me?

Rita,

See attached file:

unique-distinct-and-common-values-from-two-ranges1.xlsx

Yes, thank you, Oscar!

Hi, Oscar!

If we replace this part *(COUNTIF($J$1,$A$2:$A$11)>0) of the formula by *(IF($J$1=$D$2:$D$11,ROW($E$2:$E$11))), formula will work more correctly with big ranges.

Oscar, I need your help.

How to modify this formula - {INDEX($E$2:$E$11,MATCH(0,COUNTIF($G$1:G1,$E$2:$E$11)+COUNTIF($B$2:$B$11,$E$2:$E$11),0))} that it works with criterion "OTHER" from your last attached example file? I need values from List2 with "other" criterion are not in List1 with "other" criterion.

The rusult is II. Because in List2 II with "other" criterion, but in List1 II with "QWERTY" criterion, that's what I want - II.

Can you help?

Rita,

Your question is really interesting! But I have no answer! That makes it even more interesting.

Thanks anyway, Oscar.

[...] this VBA method.... Atlas: Excel Training | Testing | Consulting And a formula solution here.... Extract a unique distinct list from three columns in excel | Get Digital Help - Microsoft Excel reso... This is the result of the formula, it's not perfect (I can't get rid of the 0) and the result is [...]

[...] Oscars formulas. Thanks for your response Robert, I have visited the web site you given, I found Extract a unique distinct list from three columns,But this is not what I want, but I will try Pivot Table and again thanks for your [...]

Hi Oscar,

I am really amazed to see your examples in the thread "Extract a unique distinct list from three columns with possible blanks". However, Do you have the working example file for excel 2003 version? I tried to manipulate your formula used in excel 2007 but failed to get the result. Appreciate if you can share one please.

Regards,

vidya

Vidya Shankar,

how-to-extract-a-unique-list-from-three-columns-in-excelv2-excel-2003.xls

[…] Hi MickG,thanks for your answer, but I'm looking for a formula. I have searched it all over the internet and finally found an answer.Here is the link, maybe it can help others who are looking for it.Extract a unique distinct list from three columns in excel | Get Digital Help - Microsoft Excel reso… […]

Hi Oscar,

I modified your formula to work on my spreadsheet, but somehow it is giving blank cells. Maybe because it is in error.

I work in Office 2013:

=IFERROR(IFERROR(INDEX(List1;MATCH(0;IF(ISBLANK(List1);1;COUNTIF($A$11:A11;List1));0));INDEX(List2;MATCH(0;IF(ISBLANK(List2);1;COUNTIF($A$11:A11;List2));0)));"")

Your help will be highly appreciated :)

Regards

Charl

Hi !

I have tried several of these formulas, works great, but if I hit the formula bar, and hit enter it doesn't work anymore. I have also seen that there are { in front of the formula, and } after. Any clue ?

In order to execute the command you need to hit CTRL + Shift + Enter. If the formula is formatted correctly it will put the curly {} around the formula

Hi,

I used the same formula, iam getting the required result but noticed that file is taking time to save or if we update any data in any cell its taking time as well?

Hello,

I used the above formula and am getting the required result, the only problem I'm facing is that the excel is taking time to save or update, especially the coloumn were the formula is mapped. Could you please let me know how to fix this?

Hello, just wanted to say this webpage was fantastic. I adapted the formula to work with 6 columns, and this was a great solution to a tricky problem. Thank you so much!

May you please help me adapt the "Extract a unique distinct list from three columns with possible blanks" formula to 5 columns of data. Your assistance will be much appreciated. Thanks

Hi, I have been trying to use your formula to get a list of distinct years from date columns in various tables however it doesn't seem to work if a table contains only 1 row? See formula below with 1 of my table columns included:

=IFERROR(IFERROR(IFERROR(INDEX(YEAR(tblLeakTest[Date]), MATCH(0, COUNTIF($E$1:E1, YEAR(tblLeakTest[Date]))+(YEAR(tblLeakTest[Date])=""), 0)), INDEX(YEAR($B$2:$B$7), MATCH(0, COUNTIF($E$1:E1, YEAR($B$2:$B$7))+(YEAR($B$2:$B$7)=""), 0))), INDEX(YEAR($C$2:$C$12), MATCH(0, COUNTIF($E$1:E1, YEAR($C$2:$C$12))+(YEAR($C$2:$C$12)=""), 0))), "")