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.

 

[krantz44]

This information has been most useful and I believe I am close but I can't seem to finish of fthe formula for mulitple column sets of criteria.

1. On Sheet 1: I have a list of questions in C18:C200
2. I have 10 categories that the questions may be relevant
3. On Sheet 1: The categories are listed in row / cells S17:AB17
4. On Sheet 2, I want to have my inputs (Say V1:V10) where 10 categories are possible but 1 is the minimum. Depending upon the categories listed in V1:V10, the relevant questions would show up as a list in a range below.
5. Back on sheet 1, under each of the categories (S17:AB17) and for each of the questions (C17:C200), I placed an 'X' to identify that question as relevant to the respective categories.

The idea is to have a fixed list of questions that when I put in the category names on Sheet 2, will automatically generate a list of questions that i can then print and hand to someone to ask.


Below is the formula and I tried to next the OR in the IF function but when I do, it returns all of the questions, and not just the questions releveant to V5 and V6 categroies (aligned with S and T on Master/Sheet 1)

{=IFERROR(INDEX(Master!C$18:C$124,SMALL(IF(OR((Master!$S$18:$S$124=$V$5)+(Master!$T$18:$T$124=$V$6)>0),ROW(Master!C$18:C$124)-ROW(Master!C$18)+1),ROWS(C$18:C19))),"")}

Any ideas? If this had worked, I would have just added in the other 8 categories as Condditions in the OR function. I didn't know any other way.

I appreciate any help!! Jim

 

[Aladin Akyurek]

@krantz44

The results destination sheet...

V11: Q-list

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

=IFERROR(INDEX(Master!C$18:C$124,
  SMALL(IF(ISNUMBER(MATCH(Master!$S$17:$AB$17,$V$1:$V$10,0)),
  IF(Master!$S$18:$AB$124="X",ROW(Master!$S$18:$AB$124)-ROW(Master!$S$18)+1)),
  ROWS($V$12:V12))),"")

 

[krantz44]

@Aladin: Thank you!! Only maybe i failed to mention but there could be one question applicable to multiple categories and the list now containes duplicates. For example. If questions 1-5 are applicable to category A and Questions 4-7 are applicable to Category B and I want to generate a list based on Categories A and B, I am getting questions 4 and 5 duplicated.
Any way to remove that duplication?

Thanks again. Jim

 

[Aladin Akyurek]

@Aladin: Thank you!! Only maybe i failed to mention but there could be one question applicable to multiple categories and the list now containes duplicates. For example. If questions 1-5 are applicable to category A and Questions 4-7 are applicable to Category B and I want to generate a list based on Categories A and B, I am getting questions 4 and 5 duplicated.
Any way to remove that duplication?

Thanks again. Jim

Define Ivec by means of Formulas | Name Manager as referring to:

=ROW(Master!C$18:C$124)-ROW(Master!C$18)+1

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

=IFERROR(INDEX(Master!C$18:C$124),
  SMALL(IF(FREQUENCY(IF(Master!C$18:C$124)<>"",
  IF(ISNUMBER(MATCH(Master!$S$17:$AB$17,$V$1:$V$10,0)),
  IF(Master!$S$18:$AB$124="X"
  MATCH(Master!C$18:C$124,Master!C$18:C$124,0)))),
  Ivec),Ivec),ROWS(V$11:V11))),"")

Note. The foregoing is untested!...

 

[krantz44]

This worked great! thanks!

 

[Aladin Akyurek]

This worked great! thanks!

You are welcome.

 

[mrbohn]

Hello, This formula works great. I wanted to use it to separate a master list of clients assigned by a supervisor to individual staff sheets. This works as planned. What I also want to do is have the staff enter notes on these clients that will feed back to the master sheet. The problem arises when a client is removed and all other rows are bumped up one, the information from that row entered by the staff remains on the same row and is now associated with a different client. Any way to solve this?

 

[Aladin Akyurek]

Hello, This formula works great. I wanted to use it to separate a master list of clients assigned by a supervisor to individual staff sheets. This works as planned. What I also want to do is have the staff enter notes on these clients that will feed back to the master sheet. The problem arises when a client is removed and all other rows are bumped up one, the information from that row entered by the staff remains on the same row and is now associated with a different client. Any way to solve this?

Which post (#?) are you referring to?

 

[mrbohn]

I am not referring to a particular post. I have been able to use this formula correctly to spread assignments to six different sheets by using the staff name and assigned date as a qualifier. What I really need to do is figure some Macro that will take this information, create a new dated sheet on each workbook with that information saved as values, and then read back to the previous week's data using a vlookup formula which will grab notes from the clients that were there and ignore the clients which are no longer assigned. Repeated weekly with a macro/button that will recognize the most recent Monday to create tabs and carry over data from the previous week.

 

[Aladin Akyurek]

I am not referring to a particular post. I have been able to use this formula correctly to spread assignments to six different sheets by using the staff name and assigned date as a qualifier. What I really need to do is figure some Macro that will take this information, create a new dated sheet on each workbook with that information saved as values, and then read back to the previous week's data using a vlookup formula which will grab notes from the clients that were there and ignore the clients which are no longer assigned. Repeated weekly with a macro/button that will recognize the most recent Monday to create tabs and carry over data from the previous week.

I hope someone into VBA is willing to take this up.