Array Formula Not Working with Range with Formulas

[riverboatgambler]

I have an array formula that is supposed to return a list of the unique
items from another range (I6D in this case). The Array Formula is as
follows:

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

The range I6D is a named range that comprises two other named ranges,
both of which also return unique items from two other ranges.

The problem I have is that when I6D contains the array formulas that
return those inital unique items, the above array formula returns #N/A,
however, if I COPY & PASTE VALUES for I6D, the above formula then works
correctly.

Has anybody come across this problem before and got some ideas on where
I might start to fix it?

Many thanks.

 

[vezerid]

Although at the moment I am not in the position to understand what the
formula does, your problem seems to have to do with the values in ID6.
Check for space characters. They might exist in the source data so,
when you just type them they are not found, but when you Copy/Paste
they are.

Use the text functions =LEN(), =CODE(MID(A1,x,1)) to see if there are
any non-printing characters.

Does this help?
Kostis Vezerides

 

[riverboatgambler]

Kostis,

The LEN and CODE functions return the same data for both the range
filled with formulas and the "pure values" range. Since posting, I have
continued to develop the spreadsheet (primarily defining a lot more
named ranges elsewhere) and now the other Array Formulas that I use
(the ones that create the inital two Unique ranges on which the final
array formula is supposed to work) have started giving the same
symptoms. I now suspect that this is not a formula problem, but an
Excel problem - possibly related to the number of named ranges I have
in the sheet, and for some reason the Array Formulas are failing due to
what appears to be something entirely unrelated (I'm using Excel 2003
under Win XP SP2). I sincerely hope I'm wrong and that the problem is
down to ME doing something wrong, but as I see it at the moment,
something very weird is going on here.

Regards,

Huw.

 

[riverboatgambler]

Kostis,

The LEN and CODE functions return the same data for both the range
filled with formulas and the "pure values" range. Since posting, I have
continued to develop the spreadsheet (primarily defining a lot more
named ranges elsewhere) and now the other Array Formulas that I use
(the ones that create the inital two Unique ranges on which the final
array formula is supposed to work) have started giving the same
symptoms. I now suspect that this is not a formula problem, but an
Excel problem - possibly related to the number of named ranges I have
in the sheet, and for some reason the Array Formulas are failing due to
what appears to be something entirely unrelated (I'm using Excel 2003
under Win XP SP2). I sincerely hope I'm wrong and that the problem is
down to ME doing something wrong, but as I see it at the moment,
something very weird is going on here.

Regards,

Huw.

 

[vezerid]

Huw.,

I just saw your post again. How long is ID6? You might have some memory
limitations for such a long formula with too long ranges. In the end
you might have to end up breaking your ranges and reapply the formulas
in shorter ranges and then get sums of the interim formulas. I am not
too familiar yet with the limitations Excel has for such long formulas.
If you have some clearer data post again and maybe you will get a
clearer answer.

Regards,
Kostis