Filter rows and display them based on Criteria

Alan,

Thanks, but I am new to this and need more help.

I downloaded the xls... but how can I use or move those functions to my xls?

Also... I don't think I am using the formula correctly because I am getting a "Value" result. Can you explain step by step how to use that function?

Thanks,
JML

"Alan Beban" wrote:

If the functions in the freely downloadable file at
http://home.pacbell.net/beban are available to your workbook, you can
array enter into a range at least large enough to accommodate the output

=ArrayRowFilter1(dataRange,1,1001)

Alan Beban

JML wrote:

Hello,

I would like help with the following problem
I have a range that looks like this:

Job Amount
1001 1
1002 2
1002 3
1001 4

I want to have the following display on another worksheet based on a criteria field that I will set to = 1001.

Job Amount
1001 1
1001 4

Thanks in advance for the help!

JML

Frank,

Thanks for the help. This function is working, but I am wondering if you could give me an explanation. I would like to be able to expand my use of it for more than 1 criteria, and for larger tables, etc.

Thanks so much for your help!

"Frank Kabel" wrote:

Hi
in additions to Ron's suggestion you may also have a look at 'Data -
Filter - Advanced Filter'

--
Regards
Frank Kabel
Frankfurt, Germany


JML wrote:
Hello,

I would like help with the following problem
I have a range that looks like this:

Job Amount
1001 1
1002 2
1002 3
1001 4

I want to have the following display on another worksheet based on a
criteria field that I will set to = 1001.

Job Amount
1001 1
1001 4

Thanks in advance for the help!

JML

Frank,

Sorry for the confusion.... this is the solution you gave me.. any more explanation will be a big help!

=IF(ISERROR(INDEX(Sheet1!$B$1:$B$100,SMALL(IF(Shee t1!$A$2:$A$100=$B$1,ROW(Sheet1!$A$2:$A$100)),ROW(1 :1)))),"",INDEX(Sheet1!$B$1:$B$100,SMALL(IF(Sheet1 !$A$2:$A$100=$B$1,ROW(Sheet1!$A$2:$A$100)),ROW(1:1 ))))

"JML" wrote:

Frank,

Thanks for the help. This function is working, but I am wondering if you could give me an explanation. I would like to be able to expand my use of it for more than 1 criteria, and for larger tables, etc.

Thanks so much for your help!

"Frank Kabel" wrote:

Hi
in additions to Ron's suggestion you may also have a look at 'Data -
Filter - Advanced Filter'

--
Regards
Frank Kabel
Frankfurt, Germany


JML wrote:
Hello,

I would like help with the following problem
I have a range that looks like this:

Job Amount
1001 1
1002 2
1002 3
1001 4

I want to have the following display on another worksheet based on a
criteria field that I will set to = 1001.

Job Amount
1001 1
1001 4

Thanks in advance for the help!

JML

Hi
yes possible. So something like
=IF(ISERROR(INDEX(Sheet1!$B$1:$B$100,SMALL(IF((She et1!$A$2:$A$100=$B$1)
*(rng2=criteria2),ROW(Sheet1!$A$2:$A$100)),ROW(1:1 )))),"",INDEX(Sheet1!
$B$1:$B$100,SMALL(IF((Sheet1!$A$2:$A$100=$B$1)*(rn g2=criteria2),ROW(She
et1!$A$2:$A$100)),ROW(1:1))))

You may consider using two separate cells. One for calculating the
INDEX value, and a second cell for checking if the return is an error
if performance is an issue.


--
Regards
Frank Kabel
Frankfurt, Germany


JML wrote:
Frank,

Thanks for the help. This function is working, but I am wondering if
you could give me an explanation. I would like to be able to expand
my use of it for more than 1 criteria, and for larger tables, etc.

Thanks so much for your help!

"Frank Kabel" wrote:

Hi
in additions to Ron's suggestion you may also have a look at 'Data -
Filter - Advanced Filter'

--
Regards
Frank Kabel
Frankfurt, Germany


JML wrote:
Hello,

I would like help with the following problem
I have a range that looks like this:

Job Amount
1001 1
1002 2
1002 3
1001 4

I want to have the following display on another worksheet based on
a
criteria field that I will set to = 1001.

Job Amount
1001 1
1001 4

Thanks in advance for the help!

JML

Thanks!

I am still sort of lost on how this function is working though. Can you explain how it works in a little more detail?

Thanks.

"Frank Kabel" wrote:

Hi
yes possible. So something like
=IF(ISERROR(INDEX(Sheet1!$B$1:$B$100,SMALL(IF((She et1!$A$2:$A$100=$B$1)
*(rng2=criteria2),ROW(Sheet1!$A$2:$A$100)),ROW(1:1 )))),"",INDEX(Sheet1!
$B$1:$B$100,SMALL(IF((Sheet1!$A$2:$A$100=$B$1)*(rn g2=criteria2),ROW(She
et1!$A$2:$A$100)),ROW(1:1))))

You may consider using two separate cells. One for calculating the
INDEX value, and a second cell for checking if the return is an error
if performance is an issue.


--
Regards
Frank Kabel
Frankfurt, Germany


JML wrote:
Frank,

Thanks for the help. This function is working, but I am wondering if
you could give me an explanation. I would like to be able to expand
my use of it for more than 1 criteria, and for larger tables, etc.

Thanks so much for your help!

"Frank Kabel" wrote:

Hi
in additions to Ron's suggestion you may also have a look at 'Data -
Filter - Advanced Filter'

--
Regards
Frank Kabel
Frankfurt, Germany


JML wrote:
Hello,

I would like help with the following problem
I have a range that looks like this:

Job Amount
1001 1
1002 2
1002 3
1001 4

I want to have the following display on another worksheet based on
a
criteria field that I will set to = 1001.

Job Amount
1001 1
1001 4

Thanks in advance for the help!

JML

Hi
o.k. lets try to break the formula into its parts :
1. The IF function:
IF((Sheet1!$A$2:$A$100=$B$1)*(rng2=criteria2),ROW( Sheet1!$A$2:$A$100))

As condition two conditions are multiplied. You get the following type
of results
TRUE*TRUE = 1
TRUE*FALSE = 0
FALSE*TRUE = 0
FALSE*FALSE = 0

Only in the first case (if both conditions are met) This returns '1'
which represents a 'True' condition. In this case the row number is
returned by the IF function (you have to array enter this formula so
Excel loops through the entire range in the conditions)


2. The SMALL function
SMALL(IF(....),ROW(1:1))
The SMALL function now returns the nth smalles row number which is
returned from the IF function. The part ROW(1:1) returns 1 and changes
automatically to ROW(2:2) if you copy this down. So this is used to get
the 1st, 2nd, 3rd, 4th, etc smalles row number for wich the conditions
were met

3. INDEX:
INDEX(Sheet1!$B$1:$B$100,SMALL(....))
this simply returns for each row number returned by SMALL the value in
column B

4. ISERROR:
This function is used to prevent error messages after no more
conditions are met.


--
Regards
Frank Kabel
Frankfurt, Germany


JML wrote:
Thanks!

I am still sort of lost on how this function is working though. Can
you explain how it works in a little more detail?

Thanks.

"Frank Kabel" wrote:

Hi
yes possible. So something like

=IF(ISERROR(INDEX(Sheet1!$B$1:$B$100,SMALL(IF((She et1!$A$2:$A$100=$B$1)

*(rng2=criteria2),ROW(Sheet1!$A$2:$A$100)),ROW(1:1 )))),"",INDEX(Sheet1!

$B$1:$B$100,SMALL(IF((Sheet1!$A$2:$A$100=$B$1)*(rn g2=criteria2),ROW(She
et1!$A$2:$A$100)),ROW(1:1))))

You may consider using two separate cells. One for calculating the
INDEX value, and a second cell for checking if the return is an
error
if performance is an issue.


--
Regards
Frank Kabel
Frankfurt, Germany


JML wrote:
Frank,

Thanks for the help. This function is working, but I am wondering
if you could give me an explanation. I would like to be able to
expand my use of it for more than 1 criteria, and for larger
tables, etc.

Thanks so much for your help!

"Frank Kabel" wrote:

Hi
in additions to Ron's suggestion you may also have a look at 'Data
- Filter - Advanced Filter'

--
Regards
Frank Kabel
Frankfurt, Germany


JML wrote:
Hello,

I would like help with the following problem
I have a range that looks like this:

Job Amount
1001 1
1002 2
1002 3
1001 4

I want to have the following display on another worksheet based
on
a
criteria field that I will set to = 1001.

Job Amount
1001 1
1001 4

Thanks in advance for the help!

JML

Thank You Frank. You are the man!

"Frank Kabel" wrote:

Hi
o.k. lets try to break the formula into its parts :
1. The IF function:
IF((Sheet1!$A$2:$A$100=$B$1)*(rng2=criteria2),ROW( Sheet1!$A$2:$A$100))

As condition two conditions are multiplied. You get the following type
of results
TRUE*TRUE = 1
TRUE*FALSE = 0
FALSE*TRUE = 0
FALSE*FALSE = 0

Only in the first case (if both conditions are met) This returns '1'
which represents a 'True' condition. In this case the row number is
returned by the IF function (you have to array enter this formula so
Excel loops through the entire range in the conditions)


2. The SMALL function
SMALL(IF(....),ROW(1:1))
The SMALL function now returns the nth smalles row number which is
returned from the IF function. The part ROW(1:1) returns 1 and changes
automatically to ROW(2:2) if you copy this down. So this is used to get
the 1st, 2nd, 3rd, 4th, etc smalles row number for wich the conditions
were met

3. INDEX:
INDEX(Sheet1!$B$1:$B$100,SMALL(....))
this simply returns for each row number returned by SMALL the value in
column B

4. ISERROR:
This function is used to prevent error messages after no more
conditions are met.


--
Regards
Frank Kabel
Frankfurt, Germany


JML wrote:
Thanks!

I am still sort of lost on how this function is working though. Can
you explain how it works in a little more detail?

Thanks.

"Frank Kabel" wrote:

Hi
yes possible. So something like

=IF(ISERROR(INDEX(Sheet1!$B$1:$B$100,SMALL(IF((She et1!$A$2:$A$100=$B$1)

*(rng2=criteria2),ROW(Sheet1!$A$2:$A$100)),ROW(1:1 )))),"",INDEX(Sheet1!

$B$1:$B$100,SMALL(IF((Sheet1!$A$2:$A$100=$B$1)*(rn g2=criteria2),ROW(She
et1!$A$2:$A$100)),ROW(1:1))))

You may consider using two separate cells. One for calculating the
INDEX value, and a second cell for checking if the return is an
error
if performance is an issue.


--
Regards
Frank Kabel
Frankfurt, Germany


JML wrote:
Frank,

Thanks for the help. This function is working, but I am wondering
if you could give me an explanation. I would like to be able to
expand my use of it for more than 1 criteria, and for larger
tables, etc.

Thanks so much for your help!

"Frank Kabel" wrote:

Hi
in additions to Ron's suggestion you may also have a look at 'Data
- Filter - Advanced Filter'

--
Regards
Frank Kabel
Frankfurt, Germany


JML wrote:
Hello,

I would like help with the following problem
I have a range that looks like this:

Job Amount
1001 1
1002 2
1002 3
1001 4

I want to have the following display on another worksheet based
on
a
criteria field that I will set to = 1001.

Job Amount
1001 1
1001 4

Thanks in advance for the help!

JML