## Extracting unique distinct text values from a closed workbook in excel (formula)

*Article last updated on November 08, 2010*

In this post I am going to use the same formula found in this post: How to automatically create a unique distinct list and remove blanks. I have only changed the cell references to include a full path to the excel file in the formula.

**Picture of closed workbook**

The range can contain blank cells.

**Picture of extracted unique distinct text values**

Array formula in A2:

=INDEX('C:\temp\[closed workbook.xls]Sheet1'!$A$1:$A$6, MATCH(0, IF(ISBLANK('C:\temp\[closed workbook.xls]Sheet1'!$A$1:$A$6), 1, COUNTIF(A1:$A$1, ''C:\temp\[closed workbook.xls]Sheet1'!$A$1:$A$6)), 0)) + CTRL + SHIFT + ENTER copy cell and paste it down as far as needed.

An interesting observation is when I evaluate the formula I get #ref errors but the final results are correct.

5 easy ways to VLOOKUP and return multiple values

This post explains how to lookup a value and return multiple values. No array formula required.

Lookup and return multiple values concatenated into one cell

This article demonstrates how to find a value in a column and concatenate corresponding values on the same row. The […]

Question: How do I create a chart that dynamically adds the values, as i type them on the worksheet? Answer: […]

### 3 Responses to “Extracting unique distinct text values from a closed workbook in excel (formula)”

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

Use the img tag, like this: <img src="Insert pic link here">

**Contact Oscar**

You can contact me through this contact form

I tried out the unique list for the closed workbook. It works great. I thought it would not have worked because I assumed that the countif function does not work with closed workbooks. How comes in this case, the countif function works with closed workbooks?

I think it has to do with Countif(range, criteria) not accepting anything else than a cell range reference in "range" argument.

I think that is correct. It does not work with the countif duplicate formula Countif(range, criteria)>1. Most of the time, my data is contained on the same worksheet as the countif formula. Working with closed workbooks definitely slows down the calculations.