Formula will not auto calculate

I didn't look at your file.

I think the correct formula should be this:

=IFERROR(INDEX(Master1!$B$2:$B$12,MATCH(0,COUNTIF(Sheet2!$A$1:$A1,Master1!$B$2:$B$12),0)),"")

Still array entered.

Is the problem happening in an Excel file?

Your link is to a Google file. I know nothing about Google spreadsheets.

Seems to be working OK for me.

On the Master1 sheet I changed cell B12 from Brown Horse to Purple Horse and on sheet "Second attempt..." the list in column A updated as expected.

Are you sure you have calculation set to automatic?

I'll be going offline for several hours. Don't want to leave you hanging.

so i have to basically start from scratch and it worked. my excel i was working in was the issue. good thing it was a test copy.

Another Question: Do you know if there is a way to quicken the speed of checking the array. when i added new data ever new cell entered takes up to 2 minutes to update my sheet 2. any suggestions?

That particular formula will be slow to calculate on large data sets.

Here's an alternative that will be faster to calculate on large data sets. However, extracting uniques is a calculation intensive operation by its nature and may still be slow to complete.

This array formula** entered in A2 on the Second attempt sheet:

=IFERROR(INDEX(Master1!B:B,SMALL(IF(FREQUENCY(MATCH(Master1!B$2:B$12,Master1!B$2:B$12,0),ROW(Master1!B$2:B$12)-ROW(Master1!B$2)+1),ROW(Master1!B$2:B$12)),ROWS(A$2:A2))),"")

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Copy down until you get blanks.

Wow it does calculate quickly. my only issue is i am building this for anouther person to use and i dont want them touching the formula or having to drag it down each time there a new entry. so i expanded the range from B2:B2212 and when i made the correction. the cell A2 is blank with or without an array entered. but if i only plug in the search where data is, it works fine. Thoughts?

OK, use this version which will account for empty cells:

=IFERROR(INDEX(Master1!B:B,SMALL(IF(FREQUENCY(IF(Master1!B$2:B$2212<>"",MATCH(Master1!B$2:B$2212,Master1!B$2:B$2212,0)),ROW(Master1!B$2:B$2212)-ROW(Master1!B$2)+1),ROW(Master1!B$2:B$2212)),ROWS(A$2:A2))),"")

Still array entered.

You're welcome. Thanks for the feedback!


If your question has been solved please mark the thread as being solved.

In the menu bar above the very first post select Thread Tools, then select Mark this thread as solved.