If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
Filter rows and display them based on Criteria
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 |
#3
|
|||
|
|||
Filter rows and display them based on Criteria
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 |
#4
|
|||
|
|||
Filter rows and display them based on Criteria
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 |
#5
|
|||
|
|||
Filter rows and display them based on Criteria
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 |
#6
|
|||
|
|||
Filter rows and display them based on Criteria
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 |
#7
|
|||
|
|||
Filter rows and display them based on Criteria
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 |
Thread Tools | |
Display Modes | |
|
|