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

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

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 […]### 3 Responses to “Extracting unique distinct text values from a closed workbook in excel (formula)”

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

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.