can anyone explain me this formula?

[hobgoblinvasya]

... in lamers terms

=INDEX(AP$1:AP$1000,MIN(IF(COUNTIF(AQ$1:AQ1,AP$2:AP$1000)=0,IF(AP$2:AP$1000<>"",ROW(AP$2:AP$1000)))))&""

actually what this does is it produces a list w/o any duplicate entries from an original list. however i also need to ignore the string "N/A" and i have no idea where to add the condition.

any help would be very welcome

 

[hobgoblinvasya]

i think i managed to get around it by replacing "N/A" with an empty string in the source list. so i guess this thread can be classified as solved

 

[mikerickson]

If you replace
ROW(AP$2:AP$1000)
with
IF(ISERROR(AP$2:AP$1000),ROW(AP$2:AP$1000))

It should eliminate the #NA

To explain the formula:
This is an array of the row numbers of the cells in AP1:AP1000 that have values
IF(AP$2:AP$1000<>"",ROW(AP$2:AP$1000))

IF(COUNTIF(AQ$1:AQ1,AP$2:AP$1000)=0,previous array)
returns that array if no member of AQ$1:AQ1 is in AP2:AP1000

MIN(IF(COUNTIF(AQ$1:AQ1,AP$2:AP$1000)=0,previous array))
returns the lowest row number from that array

INDEX(AP$1:AP$1000,MAX(...)) returns the contents of that minimum row

INDEX(AP$1:AP$1000,MAX(...))&"" causes blanks to be displayed as "" rather than 0

 

[hobgoblinvasya]

thats the thing. N/A is a string, not an actual error.

so i guess i should replace:
ROW(AP$2:AP$1000)

with
if(ROW(AP$2:AP$1000)="N/A";ROW(AP$2:AP$1000))

thats interesting, and i will definitely see which way is faster/less ressource intensive.

as usual... a big thanks for the alternative way and an explanation !

 

[Aladin Akyurek]

thats the thing. N/A is a string, not an actual error.

so i guess i should replace:
ROW(AP$2:AP$1000)

with
if(ROW(AP$2:AP$1000)="N/A";ROW(AP$2:AP$1000))

thats interesting, and i will definitely see which way is faster/less ressource intensive.

as usual... a big thanks for the alternative way and an explanation !

if(ROW(AP$2:AP$1000)="N/A";ROW(AP$2:AP$1000))

would not yield a useful result for a row number cannot be equal to "N/A".

Maybe you meant:

if(AP$2:AP$1000="N/A"...