can anyone explain me this formula?

hobgoblinvasya

Board Regular
Joined
Jun 29, 2005
Messages
215
... 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 :)
 

Some videos you may like

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

hobgoblinvasya

Board Regular
Joined
Jun 29, 2005
Messages
215
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

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,901
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

Board Regular
Joined
Jun 29, 2005
Messages
215
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
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"...
 

Watch MrExcel Video

Forum statistics

Threads
1,122,207
Messages
5,594,841
Members
413,944
Latest member
3xc3ln00b

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top