Array formula lock-up

[lhkittle]

Excel 2010

Using J. Walkenbach's Excel 2010 Bible, page 396 there is an array formula to return a list of unique items in a range.

After entering it I did "something" or made a misstep and now i am hopelessly locked up on that worksheet, with cursor blinking in the formula bar. Iwent to the formula bar initally and tried Ctrl + Enter in the orginal formula entry cell I3 to try to get the array formula back to a 'plain' formula. By and by I am now locked up in cell I349 and a formula bar with a blinking cursor.

I've been able to fight my way out of "You can't change part of an arry" mess before but this one is out of my reach.

Thanks for any help.
Regards,
Howard

 

[lhkittle]

Excel 2010



Using J. Walkenbach's Excel 2010 Bible, page 396 there is an array formula to return a list of unique items in a range.



After entering it I did "something" or made a misstep and now i am hopelessly locked up on that worksheet, with cursor blinking in the formula bar. I went to the formula bar initally and tried Ctrl + Enter in the orginal formula entry cell I3 to try to get the array formula back to a 'plain' formula. By and by I am now locked up in cell I349 and a formula bar with a blinking cursor.



I've been able to fight my way out of "You can't change part of an arry" mess before but this one is out of my reach.



Thanks for any help.

Regards,

Howard

I was able to bust out of that lock-up by forcing a computer shut down. Plus I corrected one mistake I had going, the two columns I was working with were not identical in size. Fixed that, and on the CD from J. Walkrnbach's2010 Bible there is an exact example of what I want to do.

I named a range Data, I3:I300. It contains various names an words, severalduplicates and no blanks.

I selected the column to the right of Data, J3:J300 and copied the formula from the CD example and pasted it into cell J3, the active cell of J columnselection.

=IFERROR(INDEX(data,SMALL(IF(MATCH(data,data,0)=ROW(INDIRECT("1:"&ROWS(data))),MATCH(data,data,0),""),ROW(INDIRECT("1:"&ROWS(data))))),"")

Then I entered with Ctrl-Shift-Enter. The brackets { } appear but and nothing in the J column.

The entries in Data only go down about 165 rows, and I understand the IFERROR returns a blank cell instead of #NUM for the empty
data rows.

Can anyone see what I am doing wrong?

Thanks for any help.Regards,
Howard