Extract a List of Values Filtered by Criteria with Sub-Arrays Tutorial Help

[CobraWave]

Hi all.

I'm looking to create a formula which will let me pick out rows which contain certain parameters (in various columns).

This tutorial proceeds to explain this, but only allows for one search parameter; I'd like multiple parameters.

Extract a List of Values Filtered by Criteria with Sub-Arrays in Excel

Using the template given, I've modified the code to include an AND statement nested in the IF, theoretically solving my issue. But it doesn't work. All that is output is the very first row of the database, whether it fulfills the requirements or not.

My data structure is identical to the one given in the tutorial.

Here is my modified formula:

=IFERROR(
    INDEX('Car Data'!B$2:B$1156,
        SMALL(
            IF(
                AND(
                    'Car Data'!$H$2:$H$1156>='Filtered List'!$A$2,
                    'Car Data'!$E$2:$E$1156='Filtered List'!$B$2
                ),
                ROW('Car Data'!B$2:B$1156)-ROW('Car Data'!B$2)+1
            ),
            ROWS('Car Data'!B$2:'Car Data'!B2)
        )
    )
,"")

The value for 'Filtered List'!$A$2 is set as 35, and the value for 'Filtered List'!$B$2 is set as 3.

Working with the template given, this should search for all cars which have greater than or equal to 35mpg and exactly 3 cylinders. But like I said there's an error somewhere.

And yes, I have dragged down the selection so the array is able to output completely, but all is output is Car Data Row 2.

Thanks.

 

[Marcelo Branco]

Aladin,

I think there is a typo in your formula (= instead of , )

M.

A4, control+shift+enter, not just enter, and copy down

=IFERROR(INDEX('Project Portfolio'!A$3:A$83,
  SMALL(IF(ISNUMBER(MATCH('Project Portfolio'!$K$3:$K$83=$A$2:$A$3,0)),
ROW('Project Portfolio'!A$3:A$83)-ROW('Project Portfolio'!A$3)+1),
  ROWS($A$4:A4))),"")

Note that the formula starts in A4 and the last term is a blank, not a space (that is, "", not " ").

 

[Aladin Akyurek]

Aladin,

I think there is a typo in your formula (= instead of , )

M.

Yes, there is. We need replace the equal sign with comma in the IsNumber/Match term. Thanks for pointing that out.

 

[Trevor S]

Thanks Guys, this worked perfectly!

 

[Aladin Akyurek]

Thanks Guys, this worked perfectly!

You are welcome.

 

[psycprof]

Is there a way to use this formula but add in a command where the values cannot equal certain values if they already appear elsewhere on the new sheet?

This is what I have currently:

=IFERROR(INDEX(StudentTranscript!C$4:C$100,SMALL(IF((StudentTranscript!$B$4:$B$100=PSYNewParadigm!$H$19)*(StudentTranscript!$G$4:$G$100>PSYNewParadigm!$H$20)*(StudentTranscript!$C$4:$C$100<>PSYNewParadigm!$A$13:$A$17),ROW(StudentTranscript!C$4:C$100)-ROW(StudentTranscript!C$4)+1),ROWS(StudentTranscript!C$4:StudentTranscript!C4))), "")

Unfortunately, once I added in the 'does not equal' statement, the code returns no results. Is there anyway to do what I am trying? I want the values not to match anything in PSYNewParadigm!A6:A9, A13:A17, A21:A25, E5:E9, E13:E17. I also want it not to return "PSY 2301".

Is there a simpler way to do what I am trying perhaps? I just want to pull all of the psychology electives a student has taken. The required courses are in those cells I want to exclude. The list of courses that the student has taken is populated in StudentTranscript!C C4:C100. (Dept code, PSY, would be in column B)

Thanks.

 

[Aladin Akyurek]

Is there a way to use this formula but add in a command where the values cannot equal certain values if they already appear elsewhere on the new sheet?
[...]
I want the values not to match anything in PSYNewParadigm!A6:A9, A13:A17, A21:A25, E5:E9, E13:E17. I also want it not to return "PSY 2301".

A4, control+shift+enter, not just enter, and copy down:

[SIZE=2][FONT=lucida console]=IFERROR(INDEX(StudentTranscript!C$4:C$100,
  SMALL(IF(StudentTranscript!$B$4:$B$100=PSYNewParadigm!$H$19,
  IF(StudentTranscript!$G$4:$G$100>PSYNewParadigm!$H$20,
  IF(ISNA(MATCH(StudentTranscript!$C$4:$C$100,ExcludeList,
  ROW(StudentTranscript!C$4:C$100)-ROW(StudentTranscript!C$4)+1))),
  ROWS(A$4:A4))), "")
[/FONT][/SIZE]

In order to invoke the foregoing formula, you need to define ExcludeList via Formulas | Name Manager as referring to:

=ARRAYUNION(PSYNewParadigm!$A$6:$A$9, PSYNewParadigm!$A$13:$A$17, 
   PSYNewParadigm!$A$21:$A$25, PSYNewParadigm!$E$5:$E$9, 
   PSYNewParadigm!$E$13:$E$17)

Here is the code for ARRAYUNION which you need to install in your workbook, using Alt+F11...

you must install in your workbook, using Alt+F11...

Function ArrayUnion(ParamArray Arg() As Variant) As Variant
' Code: Juan Pablo González
' Spec: Aladin Akyurek
' May 4, 2003
' Ref: TinyURL.com - shorten that long URL into a tiny URL
Dim TempUnion() As Variant
Dim i As Long, Itm As Variant, Ctr As Long
For i = LBound(Arg) To UBound(Arg)
Arg(i) = Arg(i)
If IsArray(Arg(i)) Then
For Each Itm In Arg(i)
Ctr = Ctr + 1
ReDim Preserve TempUnion(1 To Ctr) As Variant
TempUnion(Ctr) = Itm
Next Itm
Else
Ctr = Ctr + 1
ReDim Preserve TempUnion(1 To Ctr) As Variant
TempUnion(Ctr) = Arg(i)
End If
Next i
ArrayUnion = TempUnion
End Function

 

[psycprof]

Will anyone who uses the sheet then need to install the code as well?

 

[Aladin Akyurek]

Will anyone who uses the sheet then need to install the code as well?

What does "using the sheet" mean? By the way, the MATCH bit was incomplete in the main formula... Reposted here:

=IFERROR(INDEX(StudentTranscript!C$4:C$100,
  SMALL(IF(StudentTranscript!$B$4:$B$100=PSYNewParadigm!$H$19,
  IF(StudentTranscript!$G$4:$G$100>PSYNewParadigm!$H$20,
  IF(ISNA(MATCH(StudentTranscript!$C$4:$C$100,ExcludeList,0)),
  ROW(StudentTranscript!C$4:C$100)-ROW(StudentTranscript!C$4)+1))),
  ROWS(A$4:A4))), "")

 

[psycprof]

Never mind re: the code question. I went ahead and created a named range on another sheet that had all of the courses so I would not need to create an array union, and your formula worked great. Thanks very much!

 

[Aladin Akyurek]

Never mind re: the code question. I went ahead and created a named range on another sheet that had all of the courses so I would not need to create an array union, and your formula worked great. Thanks very much!

That's great. Thanks for providing feedback.