Extracting unique distinct text values from a closed workbook in excel (formula)
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.
Related posts:
Count unique and duplicates text values in a closed workbook in excel (formula)
Extract unique distinct numbers from closed workbook in excel (formula)
Count unique and duplicate numbers from a closed workbook in excel (formula)
Extract unique distinct values from a filtered table (udf and array formula)
Recommended categories
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.