Exclusion in array formula

[Pofski]

Hello,


I might have a bit of a difficult one here :/


i am looking for a way to exlude certain rows when i am doing an array lookup.

At the moment i am using the following formula:


{=IF(ROWS(A$5:A5)>$B$2;"";INDEX('Testing Changed Extract'!A$3:A$35000;SMALL(IF(Table2[Plant]=$A$2;ROW(Table2[Plant])-ROW('Testing Changed Extract'!$A$3)+1);ROWS(A$5:A5))))}


With this, i have a drop down menu with different plants (in A2) and the array finds me all the plants and information from the tab 'Testing Changed Extract'.


Now, this works, but i should expand on it so that if there are certain parameters in another column say for example, REP and MOD, they aren't put in the list.

This is to speed up the process, cause if i choose certain plants now, it takes 5 minutes to calculate.


If needed i will try to upload an example, but it's a big file with a lot of sensitive data, and it will take me a while to adapt it.


Thank you in advance.

 

[Pofski]

https://docs.google.com/file/d/0B0naOkYo4pCmUUUxcmdKUnp2NE0/edit?usp=sharing


Ok, so here is an adapted version of the file.

Date comes in the extracted tab. This is linked with the date to calculations tab.

The test tab is being used at the moment to try and get everything done without messing up the source data.


The problem is in the Filter tab, where i am trying to get the filtering to work.


Thank you in advance.


Sincerely

 

[Luke M]

In the first part of your IF function, we can add more criteria, like so:

{=IF(ROWS(A$5:A5)>$B$2;"";INDEX('Testing Changed Extract'!A$3:A$35000;SMALL(IF((Table2[Plant]=$A$2)*(Table2[Score]>5)*(Table2[Name]<>"Bob");ROW(Table2[Plant])-ROW('Testing Changed Extract'!$A$3)+1);ROWS(A$5:A5))))}


In this example, I've added 2 more criteria to only get rows where Plant = A1, and score is greater than 5 and name does not equal "Bob". You can continue the pattern of adding criteria (by multiplying each array against the others) for items that you want to include.

 

[Pofski]

Hey Luke,


Thanks for the solution, it works now

I would just like to ask why?


I don't understand why, by multiplying, you can put conditions.


Thanks

 

[Pofski]

Also, although this works, the complete file has serveral thousands of lines where the data has to be extracted, so it's not that fast.

Does anybody have a suggestion how i can make this go faster?


Thank you

 

[NARAYANK991]

Hi Pofski ,


I have not understood your file completely , but is the Equipment ID unique ?


If so , then the complicated formula needs to be used only for deriving the values in the Equipment ID column ; thereafter , all the other columns can be populated by using the Equipment ID value in an INDEX / MATCH.


Narayan

 

[Pofski]

Hey Narayan,


Unfortunately the equipment id is not unique, but i think i could make an unique row by combining several rows.

I will just insert an extra helper row, and try to work from there.

Problem will be that they want to be able to filter on plants with exlusion of several services.


Thanks for the suggestion.


Could you also explain me why the multiplication works for added parameters?


Thanks in advance

 

[NARAYANK991]

Hi Pofski ,


In the formula you posted earlier , the IF statement ( within the SMALL function ) is working on arrays.


Suppose we assume a 3 row array i.e. only 3 rows are involved ; in the part , (Table2[Plant]=$A$2)*(Table2[Score]>5)*(Table2[Name]<>"Bob") , each of the parts results in either TRUE or FALSE ; thus , we can have :

[pre]

Array1          Array2          Array3
TRUE             TRUE           FALSE
FALSE            TRUE            TRUE
TRUE             TRUE            TRUE
Multiplication of these 3 arrays will result in one output array , as follows :

Output Array
FALSE
FALSE
TRUE

[/pre]
If you do not wish to use the multiplication operator , you can rewrite the IF statement as :


IF(Table2[Plant]=$A$2;IF(Table2[Score]>5;IF(Table2[Name]<>"Bob";ROW(Table2[Plant])-ROW('Testing Changed Extract'!$A$3)+1)))


Narayan

 

[Pofski]

Thank you for explaining it for me Naray. Now it makes sense