# can anyone explain me this formula?

#### hobgoblinvasya

##### Board Regular
... in lamers terms

Code:
``=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

### Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
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

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

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 !

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"...

Replies
0
Views
457
Replies
2
Views
245
Replies
3
Views
463
Replies
2
Views
272
Replies
4
Views
195

1,221,522
Messages
6,160,308
Members
451,637
Latest member
hvp2262

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back