Extracting unique distinct text values from a closed workbook in excel (formula)
Filed in Excel on May.12, 2010. Email This article to a Friend
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 blog posts
- Count unique and duplicates text values in a closed workbook in excel (formula)
- Count unique and duplicate numbers from a closed workbook in excel (formula)
- Extract unique distinct numbers from closed workbook in excel (formula)
- Create a drop down list containing only unique distinct alphabetically sorted text values using excel array formula
- Count unique text values in a range containing both numerical and text values
Welcome! I am Oscar and here to help you! Feel free to comment and ask excel questions.Don´t forget to add my
July 3rd, 2010 at 4:15 pm
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?
July 4th, 2010 at 9:56 pm
I think it has to do with Countif(range, criteria) not accepting anything else than a cell range reference in "range" argument.
July 5th, 2010 at 5:03 pm
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.