# Extract a unique distinct list from three columns

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

I will demonstrate two different methods for Excel 365 and earlier Excel versions. Excel 365 has fairly new functions that makes working in Excel much easier.

### Table of Contents

## 1. Extract a unique distinct list from three columns

The image above shows three different cell ranges populated with names, they are B3:B11, D3:D6, and F3:F8. The following formula works in all versions, however, I recommend Excel 365 users to go with the much smaller and easier formula in section 2.

It extracts unique distinct values from all three cell ranges B3:B11, D3:D6, and F3:F8 combined. Unique distinct means that only one instance is extracted even if there are duplicates in the data source ranges. Note that this formula does not differentiate between upper and lower case letters. In other words, A and a is the same value and a is considered a duplicate in this case.

Formula in H3:

This formula consists of three similar parts, one returns values from Col1, the second one from col2 and the third from Col3. The formula detects when values from one cell range has all been displayed and continues to the next cell range. This is made possible using the IFERROR function in three nested configurations. Two IFERROR functions are required as there are three columns. The last argument in the first IFERROR function extracts unique distinct values from the last source data range.

IFERROR(IFERROR(*formula1, formula2), formula3*)

This formula has issues if the source data ranges contain blank empty cells, section 3 demonstrates another formula that takes care of this issue.

### 1.1 Explaining formula in cell H3

#### Step 1 - Prevent duplicate values

TheÂ COUNTIF function counts values based on a condition, in this case, I am counting values in the cells above. This makes sure that duplicates are ignored.

COUNTIF($H$2:H2,$B$3:$B$11)=0

returns {TRUE;TRUE; ... ; TRUE}

#### Step 2 - Divide 1 with array

TheÂ LOOKUP function ignores errors and if we divide 1 by 0 an error occurs. 1/0 = #DIV/0!

1/(COUNTIF($F$2:F2,$B$3:$B$11)=0)

returns {1;1;... ;1}

#### Step 3 - Return value based on the array

LOOKUP(2, 1/(COUNTIF($F$2:F2,$B$3:$B$11)=0), $B$3:$B$11)

returns " Fernando " in cell F3.

#### Step 4 - Return values from Col2

When values run out from Col1 formula1 returns errors, theÂ IFERROR function then moves to formula2.

IFERROR(IFERROR(*formula1, formula2), formula3*)

formula2 is just like formula1 except that it returns values from Col2 etc.

## 2. Extract a unique distinct list from three columns - Excel 365

The image above shows three different cell ranges in cells B3:B21, D3:D8, and F3:F13. The Excel 365 formula extracts unique distinct values from three cell ranges combined. The result is an dynamic array that spills to cell H3 and cells below as far as needed. The formula is entered as a regular formula despite the fact that the formula is an Excel 365 dynamic array formula.

Excel 365 dynamic array formula in cell H3:

This formula is not considering upper and lower case letters as different values, in other words, A and a is the same value. You need a different formula that probably uses the EXACT function to differentiate between values with upper and lower case letters.

Here is a quick break-down of what the formula above does:

- (B3:B21,D3:D8,F3:F13): Some functions but not all allows you to combine cell references. This is how you do that.
- TOCOL((B3:B21,D3:D8,F3:F13)): Rearrange values so that they fit one column wide.
- UNIQUE(TOCOL((B3:B21,D3:D8,F3:F13))): Extract unique distinct values.

This formula has issues if the source data ranges contain blank empty cells, section 4 demonstrates what you need to change in order to take care of this issue. The following section 2.1 explains the formula in greater detail.

### 2.1 Explaining formula

#### Step 1 - Merge cells

Some Excel functions allow you to combine multiple cell ranges in one argument, the TOCOL function is one of those functions.

(B3:B21,D3:D8,F3:F13)

#### Step 2 - Rearrange values

The TOCOL function rearranges values in 2D cell ranges to a single column.

Function syntax: TOCOL(array, [ignore], [scan_by_col])

TOCOL((B3:B21,D3:D8,F3:F13))

returns

{"Federer, Roger "; "Djokovic, Novak "; ... ; "Frederick Southbell"}

#### Step 3 - List unique distinct values

The UNIQUE function returns a unique or unique distinct list.

Function syntax: UNIQUE(array,[by_col],[exactly_once])

UNIQUE(TOCOL((B3:B21,D3:D8,F3:F13)))

becomes

UNIQUE({"Federer, Roger "; "Djokovic, Novak "; ... ; "Frederick Southbell"})

and returns

{"Federer, Roger "; "Djokovic, Novak "; ... ; "Margeret Philips"}

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

The image above shows three different cell ranges A2:A20, B2:B7, and C2:C12. The first and last cell range has empty blank cells that the following formula takes care of. The following formula works in all versions, however, I recommend Excel 365 users to go with the much smaller and easier formula in section 4.

Array formula in D2:

The formula in cell D2 extracts unique distinct values from A2:A20, B2:B7, and C2:C12 and ignores possible blank values. This formula is not considering upper and lower case letters as different values, in other words, A and a is the same value.

### How to enter an array formula

- Select cell D2
- Press with left mouse button on in formula bar
- Paste array formula to formula bar
- Press and hold CTRL + SHIFT
- Press ENTER

## 4. Extract a unique distinct list from three columns with possible blanks - Excel 365

Excel 365 dynamic array formula in cell H3:

Check section 2 above for how the formula works.

### Get Excel *.xlsx file

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

### Unique distinct values category

First, let me explain the difference between unique values and unique distinct values, it is important you know the difference […]

This article shows how to extract unique distinct values based on a condition applied to an adjacent column using formulas. […]

Question: I have two ranges or lists (List1 and List2) from where I would like to extract a unique distinct […]

### Excel categories

### 40 Responses to “Extract a unique distinct list from three columns”

### Leave a Reply

### How to comment

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

<code>Insert your formula here.</code>

**Convert less than and larger than signs**

Use html character entities instead of less than and larger than signs.

< becomes < and > becomes >

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

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

Put your VBA code here.

[/vb]

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

Upload picture to postimage.org or imgur

Paste image link to your comment.

**Contact Oscar**

You can contact me through this contact form

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 https://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?

https://www.get-digital-help.com/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))), "")

Hi Oscar,

I've been trying to merge 3 lists excluding duplicates, blanks and numbers I have it working for 2 lists but cant get it working for 3. Any thoughts:

Combine 2 lists remove Duplicates, blanks and numbers

=IFERROR(IFERROR(INDEX(List1.1,MATCH(0,IF(ISNONTEXT(List1.1),1,COUNTIF($C$1:C17,List1.1)),0)),INDEX(List2.2,MATCH(0,IF(ISNONTEXT(List2.2),1,COUNTIF($C$1:C17,List2.2)),0))),"")

Combine 3 lists remove Duplicates, blanks and numbers ??? (not working)

=IFERROR(IFERROR(IFERROR(INDEX($A$2:$A$20,MATCH(0,IF(ISNONTEXT($A$2:$A$20),1,COUNTIF($D$1:D1,$A$2:$A$20)),0)),INDEX($B$2:$B$7,MATCH(0,IF(ISNONTEXT($B$2:$B$7),1,COUNTIF($D$1:D1,$B$2:$B$7)),0))),INDEX($C$2:$C$12,MATCH(0,IF(ISNONTEXT($C$2:$C$12),1,COUNTIF(($D$1:D1,$C$2:$C$12)),0)),"")

Regards

Andrew,

Try this formula, it must be entered in cell C2:

=IFERROR(IFERROR(IFERROR(INDEX(List1.1, MATCH(0, IF(ISNONTEXT(List1.1), 1, COUNTIF($C$1:C1, List1.1)), 0)), INDEX(List2.2, MATCH(0, IF(ISNONTEXT(List2.2), 1, COUNTIF($C$1:C1, List2.2)), 0))), INDEX(List3.3, MATCH(0, IF(ISNONTEXT(List3.3), 1, COUNTIF($C$1:C1, List3.3)), 0))),"")

I have more than 3 column. How do I add on to the formula that you have?

Hi there,

your formulas work great. But the values they produce are always in descending order.

How do I change the formula to allow for the values to be in ascending order?

And why in the first place are they in descending order?

Best regards

VH

Hi, what an excellent website ... appreciate your hardwork and admire your Excel skills. I need help in creating a formulate that will find Duplicate clock time entered for employees on a given day. e.g if John who's empID is 123 and has worked overtime from 16:00 to 18:00 twice on 9/16/2019 i.e If i have entered his overtime twice on the same date for the same clock time (16:00 - 18:00) it should highlight it. My headers are as follows:

Name | EmployeeID| Date| OT Start time| OT End Time|

Hi,

Thanks for the formula. But i am always getting "0" in first cell (K6). Kindly help.

=IFERROR(IFERROR(LOOKUP(2, 1/(COUNTIF($K$5:K5,$B$5:$B$30)=0), $B$5:$B$30), LOOKUP(2, 1/(COUNTIF($K$5:K5, $E$5:$E$30)=0), $E$5:$E$30)),LOOKUP(2, 1/(COUNTIF($K$5:K5, $H$5:$H$30)=0), $H$5:$H$30))

I love this site and I turn to it frequently. I'm a small business owner and I'm executing some report templates where there is large amounts of medical coding (and associating each code with prices I manually input) and i'm attempting to make it as automated as possible. I've run into an issue with this particular formula in that, if I go through my whole process, complete it, i'm good. however, if I amend the codes at a later date (which are in turn identified by this formula and included in the list) the numbers I've placed adjacent are now not associated with the proper code. i.e.: the unique list auto updates and forces the new code into the middle of the list, and the other codes are pushed down a cell, but the adjacent, manually inserted figures have not. Any recommendations on a work around or where i can look to resolve that issue? i'm considering timestamping but i'm not certain that's the best solution

Hi Oscar,

I've been trying to expand your formula to combine 4 lists instead of 2 but the following keeps coming back as an error; "Formula contains error". Hoping you can help me to identify where I'm going wrong.

{=IFERROR(IFERROR(INDEX(List_1,MATCH(0,IF(ISBLANK(List_1),1,COUNTIF($B$100:B100,List_1)),0)),INDEX(List_2,MATCH(0,IF(ISBLANK(List_2),1,COUNTIF($B$100:B100,List_2)),0))),INDEX(List_3,MATCH(0,IF(ISBLANK(List_3),1,COUNTIF($B$100:B100,List_3)),0)))),INDEX(List_4,MATCH(0,IF(ISBLANK(List_4),1,COUNTIF($B$100:B100,List_4)),0))))),"")}

Thanks!

Melissa

How do get a unique list with non adjacent columns? It is one list with 2 separate column headings-Type & Topping

Here is the formula for adjacent columns from one of your postings:

=INDEX($A$2:$A$4,MATCH(0,COUNTIFS($A$5:$A5,$A$2:$A$4,$B$5:$B5,$B$2:$B$4),0),COLUMN(A1))

Here is the data:

Type Topping

Coffee Sugar

Coffee Sugar

Tea English

Result:

Coffee Sugar

Tea English

What if the columns are not beside each other. Is there a way of doing this?

Here is the data:

Coffee 100 Sugar

Coffee 200 Sugar

Tea 300 English