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.

 

[Pskadot]

Staying in line with the above using the same formula, I am searching for a formula to help with the Sub-Array formula above to search key words within a comment column/row: Extracting a List of Values Filtered by Criteria

I have a comment section in Column E and would like to search the comment row for key words and if it meets these key words then copy everything single row to include all the comments entered by that individual and paste it into a new sheet in a new row.</SPAN></SPAN>

Sheet 1 Titled “Master”</SPAN></SPAN>
Column A Column B Column C Column D Column E</SPAN></SPAN>
File Number File Date Name Compliant Type of Comment </SPAN></SPAN>


Sheet 2 Titled “Control”</SPAN></SPAN>

All the data in Sheet one is transferred to this new sheet if it meets the filtered criteria by the compliant type</SPAN>
In sheet 2 “Control” Column A Row 1 has a filter name of the type of compliant </SPAN>
Formula is: </SPAN>
{=IFERROR(INDEX('MASTER'!A$1:A$35,SMALL(IF('MASTER'!$D$1:$D$35='Compliant'!$A$1:$B$1,ROW('MASTER'!A$1:A$35)-ROW('MASTER'!A$1)+1),ROWS('MASTER'!A$1:'MASTER'!A1))),"")</SPAN>

Having problems and I’m confused as to what kind of formula can be used and where would I put it within the above formula.</SPAN>

Any help would be appreciative. Thanks All</SPAN>​

 

[Aladin Akyurek]

@Psikadot

Where do you implementing the formula in the Control sheet?

The formula seems to test whether the D range in Master meets the criteria in A1:B1 of the Compliant sheet, right?

 

[Pskadot]

Aladin,

Im trying to figure what kind of formula would work best and where would I put it within the main formula.

I have a comment column and within each row of the comment section individuals would input the comment. What I am trying to do is pull keywords from the comment section and if it meets certain key words then pull the whole row with the comment into another sheet.

I would possible need to create a filter to look for the key words within the comment section.

Any help would be greatly appreciative.

Thanks.

pskadot

@Psikadot

Where do you implementing the formula in the Control sheet?

The formula seems to test whether the D range in Master meets the criteria in A1:B1 of the Compliant sheet, right?

 

[Aladin Akyurek]

Aladin,

Im trying to figure what kind of formula would work best and where would I put it within the main formula.

I have a comment column and within each row of the comment section individuals would input the comment. What I am trying to do is pull keywords from the comment section and if it meets certain key words then pull the whole row with the comment into another sheet.

I would possible need to create a filter to look for the key words within the comment section.

Any help would be greatly appreciative.

Thanks.

pskadot

Your lay-out and the task you want to achieve have become less clear (to me). Perhaps you can provide small samples from the relevant sheets along with the desired results.

 

[Pskadot]

hopefully it is much clearer: "Column a through Column D have specific title"

sheet one called control
column a column b column c column d
control # name number comment


sheet two - called info from comment
row 1 column a - is a filter section with key words

column a
row 2 - would be a formula similar to the one in my post but would have a search function to look for key words in the filter row in the comment sections and return the whole row from the control sheet.

Hopefully that makes sense.

Filter words could be anything such as "you,me, excel, number" the comment section on the original sheet "control" is inputted by other individuals and would have key words within it.

 

[Aladin Akyurek]

hopefully it is much clearer: "Column a through Column D have specific title"

sheet one called control
column a column b column c column d
control # name number comment


sheet two - called info from comment
row 1 column a - is a filter section with key words

column a
row 2 - would be a formula similar to the one in my post but would have a search function to look for key words in the filter row in the comment sections and return the whole row from the control sheet.

Hopefully that makes sense.

Filter words could be anything such as "you,me, excel, number" the comment section on the original sheet "control" is inputted by other individuals and would have key words within it.

info, A2: Idx, B2: control #, C2: name, D2: number, and E2: comment

info, A3, control+shift+enter, not just enter, and copy down:

=IFERROR(SMALL(IF(ISNUMBER(SEARCH(" "&$A$1&" ",control!$D$2:$D$100)),
  ROW(control!$D$2:$D$100)-ROW(control!$D$2)+1),ROWS($A$3:A3))),"")

info, B3, copied across:

=IF($A3="","",INDEX(control!$A$2:$D$100,$A3,
  MATCH(B$2,control!$A$1:$D$1,0)))

 

[Pskadot]

Aladin, Where does sheet two come into play - Im Confused Please help

I'll try to elaborate further:

1) Sheet1 is named Control

Columns A B C D
Row 1 (Titles) Control# Name Number Comment
Row 2 Here in this row individuals input information in this sheet time Control
Row 3 Here in this row individuals input information in this sheet time Control
Row 4 Here in this row individuals input information in this sheet time Control



2) Sheet2 is named Comment

Columns A B C D
Row 1 (Filtered row with key word) You Me Excel Number
Row 2 What I am trying to do is seach for the filter words in sheet 1 where the comment column is and return to sheet 2 all rows within that comment. So if row 2 from sheet 1 on comment row has "you" in it then return everything within row 2 sheet 1 to include all the comment in that row.
Row 3
Row 4


Im trying the formula you provided but i am lost. Please help Thanks

 

[Aladin Akyurek]

Aladin, Where does sheet two come into play - Im Confused Please help

[...]

No wonder you are confused...

You wrote:

[...]

Sheet 1 Titled “Master”</SPAN></SPAN>

[...]

Sheet 2 Titled “Control”</SPAN></SPAN>

[...]

[...]

sheet one called control
column a column b column c column d
control # name number comment


sheet two - called info from comment
row 1 column a - is a filter section with key words

[...]

Now you write:

I'll try to elaborate further:

1) Sheet1 is named Control

[...]

2) Sheet2 is named Comment

[...]

Im trying the formula you provided but i am lost. Please help Thanks

You seem also to confound rows and columns...

RE-CAP

Go to Comment.

A1 houses a key word.

Enter the following

Idx in A2,

control # in B2,

name in C2,

number in D2,

and comment in E2.

In A3 of Comment, control+shift+enter, not just enter, and copy down:

=IFERROR(SMALL(IF(ISNUMBER(SEARCH(" "&$A$1&" ",control!$D$2:$D$100)),
  ROW(control!$D$2:$D$100)-ROW(control!$D$2)+1),ROWS($A$3:A3))),"")

Control+shift+enter: Press down the control and the shift keys at the same time while you hit the enter key. If done properly, Excel will put { and } around the formula.

In B3 of Comment, just enter, copy across, and down:

=IF($A3="","",INDEX(control!$A$2:$D$100,$A3,
  MATCH(B$2,control!$A$1:$D$1,0)))

 

[Pskadot]

Aladin,

Thank you.

I followed step by step and it return the following:

=IFERROR(SMALL(IF(ISNUMBER(SEARCH(" "&$A$1&" ",control!$D$2:$D$100)),ROW(control!$D$2:$D$100)-ROW(control!$D$2)+1),ROWS($A$3:A3))),"")

Microsoft Excel
You've have entered too few arguments for this function.
To get help with this function , click OK to close this message. Then click the Insert Function located to the left of the equal sign in your formula.

im really need help

 

[Aladin Akyurek]

Aladin,

Thank you.

I followed step by step and it return the following:

=IFERROR(SMALL(IF(ISNUMBER(SEARCH(" "&$A$1&" ",control!$D$2:$D$100)),ROW(control!$D$2:$D$100)-ROW(control!$D$2)+1),ROWS($A$3:A3))),"")

Microsoft Excel
You've have entered too few arguments for this function.
To get help with this function , click OK to close this message. Then click the Insert Function located to the left of the equal sign in your formula.

im really need help

Just one paren too many...

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

=IFERROR(SMALL(IF(ISNUMBER(SEARCH(" "&$A$1&" ",control!$D$2:$D$100)),
  ROW(control!$D$2:$D$100)-ROW(control!$D$2)+1),ROWS($A$3:A3)),"")