| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
extract matching vales
I am looking for array formula for
range a2:a1200 Rajiv Raju Anita Rajan Prem Ram extract all the text values from the range that begins with "raj" string(not case sensitive) Output results be: Rajiv Raju Rajan |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
extract matching vales
Hi!
Here's the "quick and dirty" version: =INDEX(A$2:A$1200,SMALL(IF(LEFT(A$2:A$1200,3)="raj ",ROW($1:$1199)),ROW(A1))) Here's the "robust" version: =IF(ROWS($1:1)<=COUNTIF(A$2:A$1200,"Raj*"),INDEX(A $2:A$1200,SMALL(IF(LEFT(A$2:A$1200,3)="raj",ROW(A$ 2:A$1200)-ROW(A$2)+1),ROWS($1:1))),"") Copy down. Both array entered. Biff "TUNGANA KURMA RAJU" wrote in message ... I am looking for array formula for range a2:a1200 Rajiv Raju Anita Rajan Prem Ram extract all the text values from the range that begins with "raj" string(not case sensitive) Output results be: Rajiv Raju Rajan |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
extract matching vales
You will need to use the Data-- filter -- Advanced filter option and set
this array as the data range. In an empty cell put the field name of this array and in the cell just below this put "Raj*". In another cell again put this field name once again. Then when you go to the Data-- filter -- Advanced filter option, Click on the "copy to another location button" List range = this array including the field name Criteria range is the two cells - field name and the cell with Raj* output range is just the second cell with the field name. if you need only the unique records, you could click on that check box as well. only unique records will then be extracted. See if this works. Manoj "TUNGANA KURMA RAJU" wrote: I am looking for array formula for range a2:a1200 Rajiv Raju Anita Rajan Prem Ram extract all the text values from the range that begins with "raj" string(not case sensitive) Output results be: Rajiv Raju Rajan |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
extract matching vales
Hi,Biff ,Thanks it worked great(robust version).I would like to understand
your formula in depth.Can you please explain me step by step "Biff" wrote: Hi! Here's the "quick and dirty" version: =INDEX(A$2:A$1200,SMALL(IF(LEFT(A$2:A$1200,3)="raj ",ROW($1:$1199)),ROW(A1))) Here's the "robust" version: =IF(ROWS($1:1)<=COUNTIF(A$2:A$1200,"Raj*"),INDEX(A $2:A$1200,SMALL(IF(LEFT(A$2:A$1200,3)="raj",ROW(A$ 2:A$1200)-ROW(A$2)+1),ROWS($1:1))),"") Copy down. Both array entered. Biff "TUNGANA KURMA RAJU" wrote in message ... I am looking for array formula for range a2:a1200 Rajiv Raju Anita Rajan Prem Ram extract all the text values from the range that begins with "raj" string(not case sensitive) Output results be: Rajiv Raju Rajan |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
extract matching vales
"TUNGANA KURMA RAJU" wrote...
Hi,Biff ,Thanks it worked great(robust version).I would like to understand your formula in depth.Can you please explain me step by step Sure! Here is an explanation I wrote for another poster. Both formulas (the one you're using and the one in this explanation) work exactly the same way. The only difference is the logical test. In your formula that test is: IF(LEFT(A$2:A$1200,3)="raj". In this explanation that test is: IF(B$2:B$8="vac". So, wherever you see mention of IF(B$2:B$8="vac", you can just substitute your logical test. ******************************** =IF(ROWS($1:1)<=COUNTIF(B$2:B$8,"vac"),INDEX(A$2:A $8,SMALL(IF(B$2:B$8="vac",ROW(A$2:A$8)-ROW(A$2)+1),ROWS($1:1))),"") The only part of the formula that you actually need is this: INDEX(A$2:A$8,SMALL(IF(B$2:B$8="vac",ROW(A$2:A$8)-ROW(A$2)+1),ROWS($1:1))) However, if you drag copy down, once the data that meets the criteria is exhausted the next cell(s) will return #NUM! errors. Errors are unsightly and can cause problems in any downstream calculations. We can build an error trap in the formula that catches these errors so that they're not displayed and won't affect any downstream calculations. Excel has some error testing functions like : Iserror, Isna, Error.Type. Using the Iserror function to test for errors and "trap" them, the formula would look like this: =IF(ISERROR(SMALL(IF(B$2:B$8="vac",ROW(A$2:A$8)-ROW(A$2)+1),ROWS($1:1))),"",INDEX(A$2:A$8,SMALL(IF (B$2:B$8="vac",ROW(A$2:A$8)-ROW(A$2)+1),ROWS($1:1)))) As you can see, this makes the formula about twice as long and, if I was still using the Sheet references, this would make it even longer! Long formulas tend to "scare" people! Not only is the formula long but when the error trap evaluates to FALSE (no error) the formula has to process the data twice. So naturally, that takes twice as long. I used a "psuedo" error trap that effectively does the same thing but is much shorter to express and is more efficient: =IF(ROWS($1:1)<=COUNTIF(B$2:B$8,"vac") =IF(ISERROR(SMALL(IF(B$2:B$8="vac",ROW(A$2:A$8)-ROW(A$2)+1),ROWS($1:1))) With the error trap I've used the formula only has to process the data once. The logic is that you count the number of instances that meet the criteria: COUNTIF(B$2:B$8,"vac") Then compare that to the number of cells that the formula is being copied to: ROWS($1:1) When you drag copy down to more cells the ROWS($1:1) function will increment to $1:2, $1:3 etc. This is compared to COUNTIF(B$2:B$8,"vac") and based on your posted example, COUNTIF(B$2:B$8,"vac") = 2. So, we end up with this: =IF(1<=2,value_if_true,value_if_false) =IF(2<=2,value_if_true,value_if_false) =IF(3<=2,value_if_true,value_if_false) etc The value_if_true argument is: INDEX(A$2:A$8,SMALL(IF(B$2:B$8="vac",ROW(A$2:A$8)-ROW(A$2)+1),ROWS($1:1)))) The value_if_false argument is: "" Returns a blank cell instead of an error, #NUM! Now, let's see what's happening when the value_if_true argument is met. INDEX(A$2:A$8,SMALL(IF(B$2:B$8="vac",ROW(A$2:A$8)-ROW(A$2)+1),ROWS($1:1)))) Ok, there's an indexed range of values, INDEX(A$2:A$8, which are the dates. There are a total of 7 elements in the range A$2:A$8. The Index function holds these elements in a relative order. That order is the total number of elements. There are 7 elements so the order is 1,2,3,4,5,6,7 whe A2 = 1 A3 = 2 A4 = 3 ... A8 = 7 Now we need to tell the formula which elements of that range to return based on meeting the criteria. That criteria is: IF(B$2:B$8="vac" This will return an array of TRUE's or FALSE's. Based on the posted example that would be: FALSE FALSE TRUE FALSE FALSE TRUE FALSE Ok, the value_if_true argument is: ROW(A$2:A$8)-ROW(A$2)+1 And the value_if_false argument is nothing. No value_if_false argument was defined and when that happens the default return is FALSE. We'll see how that comes into play later on. Back to the value_if_true argument: ROW(A$2:A$8)-ROW(A$2)+1 Since the INDEX function has a total of 7 elements indexed (1,2,3,4,5,6,7), we need a means of generating an array of numbers from 1 to 7 that correspond to the indexed elements. That's where ROW comes in handy. ROW(A$2:A$8) generates an array of 7 numbers but that array is 2,3,4,5,6,7,8 and that array does not correspond to the indexed array of 1,2,3,4,5,6,7. To take care of that we subtract the offset then add 1: -ROW(A$2)+1 This is how that is processed in the formula: 2 - 2 +1 = 1 3 - 2 + 1 =2 4 - 2 + 1 =3 5 - 2 + 1 =4 ... 8 - 2 + 1 =7 Now we have our array from 1 to 7 that correspond to the indexed array of 1 to 7. There are other ways to generate that array but this is the most "foolproof". So, now we put this all together to generate yet another array: If TRUE = ROW number, if FALSE = FALSE: B2 = vac = FALSE = FALSE B3 = vac = FALSE = FALSE B4 = vac = TRUE = 3 B5 = vac = FALSE = FALSE B6 = vac = FALSE = FALSE B7 = vac = TRUE = 6 B8 = vac = FALSE = FALSE That array is then passed to the SMALL function: SMALL({F,F,3,F,F,6,F},ROWS($1:1)) As is, that evaluates to the first smallest value which is 3. When drag copied down the ROWS function will increment to $1:2 for the second smallest, $1:3 for the third smallest, etc. Since there is no third smallest that would generate a #NUM! error but remember, we have that taken care of using our "psuedo" error trap. Putting it all together. When copied down this is what you get: INDEX(A$2:A$8,3) = the 3 rd element of the indexed array = 1/4 INDEX(A$2:A$8,6) = the 6 th element of the indexed array = 1/7 INDEX(A$2:A$8,#NUM!) = "" (blank) There you have it! Biff |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
extract matching vales
Biff,
Where did you learn this??? Classes? Books??? Thanks :) Julie "Biff" wrote: "TUNGANA KURMA RAJU" wrote... Hi,Biff ,Thanks it worked great(robust version).I would like to understand your formula in depth.Can you please explain me step by step Sure! Here is an explanation I wrote for another poster. Both formulas (the one you're using and the one in this explanation) work exactly the same way. The only difference is the logical test. In your formula that test is: IF(LEFT(A$2:A$1200,3)="raj". In this explanation that test is: IF(B$2:B$8="vac". So, wherever you see mention of IF(B$2:B$8="vac", you can just substitute your logical test. ******************************** =IF(ROWS($1:1)<=COUNTIF(B$2:B$8,"vac"),INDEX(A$2:A $8,SMALL(IF(B$2:B$8="vac",ROW(A$2:A$8)-ROW(A$2)+1),ROWS($1:1))),"") The only part of the formula that you actually need is this: INDEX(A$2:A$8,SMALL(IF(B$2:B$8="vac",ROW(A$2:A$8)-ROW(A$2)+1),ROWS($1:1))) However, if you drag copy down, once the data that meets the criteria is exhausted the next cell(s) will return #NUM! errors. Errors are unsightly and can cause problems in any downstream calculations. We can build an error trap in the formula that catches these errors so that they're not displayed and won't affect any downstream calculations. Excel has some error testing functions like : Iserror, Isna, Error.Type. Using the Iserror function to test for errors and "trap" them, the formula would look like this: =IF(ISERROR(SMALL(IF(B$2:B$8="vac",ROW(A$2:A$8)-ROW(A$2)+1),ROWS($1:1))),"",INDEX(A$2:A$8,SMALL(IF (B$2:B$8="vac",ROW(A$2:A$8)-ROW(A$2)+1),ROWS($1:1)))) As you can see, this makes the formula about twice as long and, if I was still using the Sheet references, this would make it even longer! Long formulas tend to "scare" people! Not only is the formula long but when the error trap evaluates to FALSE (no error) the formula has to process the data twice. So naturally, that takes twice as long. I used a "psuedo" error trap that effectively does the same thing but is much shorter to express and is more efficient: =IF(ROWS($1:1)<=COUNTIF(B$2:B$8,"vac") =IF(ISERROR(SMALL(IF(B$2:B$8="vac",ROW(A$2:A$8)-ROW(A$2)+1),ROWS($1:1))) With the error trap I've used the formula only has to process the data once. The logic is that you count the number of instances that meet the criteria: COUNTIF(B$2:B$8,"vac") Then compare that to the number of cells that the formula is being copied to: ROWS($1:1) When you drag copy down to more cells the ROWS($1:1) function will increment to $1:2, $1:3 etc. This is compared to COUNTIF(B$2:B$8,"vac") and based on your posted example, COUNTIF(B$2:B$8,"vac") = 2. So, we end up with this: =IF(1<=2,value_if_true,value_if_false) =IF(2<=2,value_if_true,value_if_false) =IF(3<=2,value_if_true,value_if_false) etc The value_if_true argument is: INDEX(A$2:A$8,SMALL(IF(B$2:B$8="vac",ROW(A$2:A$8)-ROW(A$2)+1),ROWS($1:1)))) The value_if_false argument is: "" Returns a blank cell instead of an error, #NUM! Now, let's see what's happening when the value_if_true argument is met. INDEX(A$2:A$8,SMALL(IF(B$2:B$8="vac",ROW(A$2:A$8)-ROW(A$2)+1),ROWS($1:1)))) Ok, there's an indexed range of values, INDEX(A$2:A$8, which are the dates. There are a total of 7 elements in the range A$2:A$8. The Index function holds these elements in a relative order. That order is the total number of elements. There are 7 elements so the order is 1,2,3,4,5,6,7 whe A2 = 1 A3 = 2 A4 = 3 ... A8 = 7 Now we need to tell the formula which elements of that range to return based on meeting the criteria. That criteria is: IF(B$2:B$8="vac" This will return an array of TRUE's or FALSE's. Based on the posted example that would be: FALSE FALSE TRUE FALSE FALSE TRUE FALSE Ok, the value_if_true argument is: ROW(A$2:A$8)-ROW(A$2)+1 And the value_if_false argument is nothing. No value_if_false argument was defined and when that happens the default return is FALSE. We'll see how that comes into play later on. Back to the value_if_true argument: ROW(A$2:A$8)-ROW(A$2)+1 Since the INDEX function has a total of 7 elements indexed (1,2,3,4,5,6,7), we need a means of generating an array of numbers from 1 to 7 that correspond to the indexed elements. That's where ROW comes in handy. ROW(A$2:A$8) generates an array of 7 numbers but that array is 2,3,4,5,6,7,8 and that array does not correspond to the indexed array of 1,2,3,4,5,6,7. To take care of that we subtract the offset then add 1: -ROW(A$2)+1 This is how that is processed in the formula: 2 - 2 +1 = 1 3 - 2 + 1 =2 4 - 2 + 1 =3 5 - 2 + 1 =4 ... 8 - 2 + 1 =7 Now we have our array from 1 to 7 that correspond to the indexed array of 1 to 7. There are other ways to generate that array but this is the most "foolproof". So, now we put this all together to generate yet another array: If TRUE = ROW number, if FALSE = FALSE: B2 = vac = FALSE = FALSE B3 = vac = FALSE = FALSE B4 = vac = TRUE = 3 B5 = vac = FALSE = FALSE B6 = vac = FALSE = FALSE B7 = vac = TRUE = 6 B8 = vac = FALSE = FALSE That array is then passed to the SMALL function: SMALL({F,F,3,F,F,6,F},ROWS($1:1)) As is, that evaluates to the first smallest value which is 3. When drag copied down the ROWS function will increment to $1:2 for the second smallest, $1:3 for the third smallest, etc. Since there is no third smallest that would generate a #NUM! error but remember, we have that taken care of using our "psuedo" error trap. Putting it all together. When copied down this is what you get: INDEX(A$2:A$8,3) = the 3 rd element of the indexed array = 1/4 INDEX(A$2:A$8,6) = the 6 th element of the indexed array = 1/7 INDEX(A$2:A$8,#NUM!) = "" (blank) There you have it! Biff |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
extract matching vales
I would dum it further with an ordinary AutoFilter by adding a column with
the first 3 letters using =left(a3,3). Put the AutoFilter on the new column with a header and pick "Raj" under the down arrow. Advanced Filter frustrates me. "Manoj" wrote: You will need to use the Data-- filter -- Advanced filter option and set this array as the data range. In an empty cell put the field name of this array and in the cell just below this put "Raj*". In another cell again put this field name once again. Then when you go to the Data-- filter -- Advanced filter option, Click on the "copy to another location button" List range = this array including the field name Criteria range is the two cells - field name and the cell with Raj* output range is just the second cell with the field name. if you need only the unique records, you could click on that check box as well. only unique records will then be extracted. See if this works. Manoj "TUNGANA KURMA RAJU" wrote: I am looking for array formula for range a2:a1200 Rajiv Raju Anita Rajan Prem Ram extract all the text values from the range that begins with "raj" string(not case sensitive) Output results be: Rajiv Raju Rajan |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
extract matching vales
Where did you learn this??? Classes? Books???
No classes. Do they teach this stuff in schools? This stuff hadn't been invented yet when I was in school! <g Books? I have a few books but none of them goes into any "depth" beyond the very basics. Like: "To sum a range of numbers use this formula: =SUM(A1:A10)." Doh! I learned the majority of what I know from these newsgroups. Seeing "real world" problems and the solutions offered and trying them out. When I saw a formula I didn't understand I would dissect it piece by piece and see how all the different pieces related to each other. One of the best tools I've found for doing this is included in Excel itself. In versions of Excel, 2002 and up, there is a tool called Evaluate Formula. In the menu ToolsFormula AuditingEvaluate Formula. This will step through the formula and show you exactly how Excel processes the formula. This is a very good tool for troubleshooting. You have to be careful, though. Some really "complex" formulas will cause Excel to crash! At least, my version does. If I use the Evaluate Formula command on the formulas I've noted in this thread Excel will crash. I've learned over time what types of fomulas will cause this and avoid evaluating them. Mostly because I already know how they work so there's really no need to evaluate. In my opinion, you will learn more from these newsgroups than you will from any book or any class. You just have to "invest" some time. The more time you invest, the more you'll learn! Biff "Julie" wrote in message ... Biff, Where did you learn this??? Classes? Books??? Thanks :) Julie "Biff" wrote: "TUNGANA KURMA RAJU" wrote... Hi,Biff ,Thanks it worked great(robust version).I would like to understand your formula in depth.Can you please explain me step by step Sure! Here is an explanation I wrote for another poster. Both formulas (the one you're using and the one in this explanation) work exactly the same way. The only difference is the logical test. In your formula that test is: IF(LEFT(A$2:A$1200,3)="raj". In this explanation that test is: IF(B$2:B$8="vac". So, wherever you see mention of IF(B$2:B$8="vac", you can just substitute your logical test. ******************************** =IF(ROWS($1:1)<=COUNTIF(B$2:B$8,"vac"),INDEX(A$2:A $8,SMALL(IF(B$2:B$8="vac",ROW(A$2:A$8)-ROW(A$2)+1),ROWS($1:1))),"") The only part of the formula that you actually need is this: INDEX(A$2:A$8,SMALL(IF(B$2:B$8="vac",ROW(A$2:A$8)-ROW(A$2)+1),ROWS($1:1))) However, if you drag copy down, once the data that meets the criteria is exhausted the next cell(s) will return #NUM! errors. Errors are unsightly and can cause problems in any downstream calculations. We can build an error trap in the formula that catches these errors so that they're not displayed and won't affect any downstream calculations. Excel has some error testing functions like : Iserror, Isna, Error.Type. Using the Iserror function to test for errors and "trap" them, the formula would look like this: =IF(ISERROR(SMALL(IF(B$2:B$8="vac",ROW(A$2:A$8)-ROW(A$2)+1),ROWS($1:1))),"",INDEX(A$2:A$8,SMALL(IF (B$2:B$8="vac",ROW(A$2:A$8)-ROW(A$2)+1),ROWS($1:1)))) As you can see, this makes the formula about twice as long and, if I was still using the Sheet references, this would make it even longer! Long formulas tend to "scare" people! Not only is the formula long but when the error trap evaluates to FALSE (no error) the formula has to process the data twice. So naturally, that takes twice as long. I used a "psuedo" error trap that effectively does the same thing but is much shorter to express and is more efficient: =IF(ROWS($1:1)<=COUNTIF(B$2:B$8,"vac") =IF(ISERROR(SMALL(IF(B$2:B$8="vac",ROW(A$2:A$8)-ROW(A$2)+1),ROWS($1:1))) With the error trap I've used the formula only has to process the data once. The logic is that you count the number of instances that meet the criteria: COUNTIF(B$2:B$8,"vac") Then compare that to the number of cells that the formula is being copied to: ROWS($1:1) When you drag copy down to more cells the ROWS($1:1) function will increment to $1:2, $1:3 etc. This is compared to COUNTIF(B$2:B$8,"vac") and based on your posted example, COUNTIF(B$2:B$8,"vac") = 2. So, we end up with this: =IF(1<=2,value_if_true,value_if_false) =IF(2<=2,value_if_true,value_if_false) =IF(3<=2,value_if_true,value_if_false) etc The value_if_true argument is: INDEX(A$2:A$8,SMALL(IF(B$2:B$8="vac",ROW(A$2:A$8)-ROW(A$2)+1),ROWS($1:1)))) The value_if_false argument is: "" Returns a blank cell instead of an error, #NUM! Now, let's see what's happening when the value_if_true argument is met. INDEX(A$2:A$8,SMALL(IF(B$2:B$8="vac",ROW(A$2:A$8)-ROW(A$2)+1),ROWS($1:1)))) Ok, there's an indexed range of values, INDEX(A$2:A$8, which are the dates. There are a total of 7 elements in the range A$2:A$8. The Index function holds these elements in a relative order. That order is the total number of elements. There are 7 elements so the order is 1,2,3,4,5,6,7 whe A2 = 1 A3 = 2 A4 = 3 ... A8 = 7 Now we need to tell the formula which elements of that range to return based on meeting the criteria. That criteria is: IF(B$2:B$8="vac" This will return an array of TRUE's or FALSE's. Based on the posted example that would be: FALSE FALSE TRUE FALSE FALSE TRUE FALSE Ok, the value_if_true argument is: ROW(A$2:A$8)-ROW(A$2)+1 And the value_if_false argument is nothing. No value_if_false argument was defined and when that happens the default return is FALSE. We'll see how that comes into play later on. Back to the value_if_true argument: ROW(A$2:A$8)-ROW(A$2)+1 Since the INDEX function has a total of 7 elements indexed (1,2,3,4,5,6,7), we need a means of generating an array of numbers from 1 to 7 that correspond to the indexed elements. That's where ROW comes in handy. ROW(A$2:A$8) generates an array of 7 numbers but that array is 2,3,4,5,6,7,8 and that array does not correspond to the indexed array of 1,2,3,4,5,6,7. To take care of that we subtract the offset then add 1: -ROW(A$2)+1 This is how that is processed in the formula: 2 - 2 +1 = 1 3 - 2 + 1 =2 4 - 2 + 1 =3 5 - 2 + 1 =4 ... 8 - 2 + 1 =7 Now we have our array from 1 to 7 that correspond to the indexed array of 1 to 7. There are other ways to generate that array but this is the most "foolproof". So, now we put this all together to generate yet another array: If TRUE = ROW number, if FALSE = FALSE: B2 = vac = FALSE = FALSE B3 = vac = FALSE = FALSE B4 = vac = TRUE = 3 B5 = vac = FALSE = FALSE B6 = vac = FALSE = FALSE B7 = vac = TRUE = 6 B8 = vac = FALSE = FALSE That array is then passed to the SMALL function: SMALL({F,F,3,F,F,6,F},ROWS($1:1)) As is, that evaluates to the first smallest value which is 3. When drag copied down the ROWS function will increment to $1:2 for the second smallest, $1:3 for the third smallest, etc. Since there is no third smallest that would generate a #NUM! error but remember, we have that taken care of using our "psuedo" error trap. Putting it all together. When copied down this is what you get: INDEX(A$2:A$8,3) = the 3 rd element of the indexed array = 1/4 INDEX(A$2:A$8,6) = the 6 th element of the indexed array = 1/7 INDEX(A$2:A$8,#NUM!) = "" (blank) There you have it! Biff |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
extract matching vales
Biff,
What a marvellous explanation and logic.Really you are great .Simply watching the formula is never reflects depth of logic behind it.As you said these things can't be taught in classes.No books have this day to day practical solutions.Thank you. "Biff" wrote: "TUNGANA KURMA RAJU" wrote... Hi,Biff ,Thanks it worked great(robust version).I would like to understand your formula in depth.Can you please explain me step by step Sure! Here is an explanation I wrote for another poster. Both formulas (the one you're using and the one in this explanation) work exactly the same way. The only difference is the logical test. In your formula that test is: IF(LEFT(A$2:A$1200,3)="raj". In this explanation that test is: IF(B$2:B$8="vac". So, wherever you see mention of IF(B$2:B$8="vac", you can just substitute your logical test. ******************************** =IF(ROWS($1:1)<=COUNTIF(B$2:B$8,"vac"),INDEX(A$2:A $8,SMALL(IF(B$2:B$8="vac",ROW(A$2:A$8)-ROW(A$2)+1),ROWS($1:1))),"") The only part of the formula that you actually need is this: INDEX(A$2:A$8,SMALL(IF(B$2:B$8="vac",ROW(A$2:A$8)-ROW(A$2)+1),ROWS($1:1))) However, if you drag copy down, once the data that meets the criteria is exhausted the next cell(s) will return #NUM! errors. Errors are unsightly and can cause problems in any downstream calculations. We can build an error trap in the formula that catches these errors so that they're not displayed and won't affect any downstream calculations. Excel has some error testing functions like : Iserror, Isna, Error.Type. Using the Iserror function to test for errors and "trap" them, the formula would look like this: =IF(ISERROR(SMALL(IF(B$2:B$8="vac",ROW(A$2:A$8)-ROW(A$2)+1),ROWS($1:1))),"",INDEX(A$2:A$8,SMALL(IF (B$2:B$8="vac",ROW(A$2:A$8)-ROW(A$2)+1),ROWS($1:1)))) As you can see, this makes the formula about twice as long and, if I was still using the Sheet references, this would make it even longer! Long formulas tend to "scare" people! Not only is the formula long but when the error trap evaluates to FALSE (no error) the formula has to process the data twice. So naturally, that takes twice as long. I used a "psuedo" error trap that effectively does the same thing but is much shorter to express and is more efficient: =IF(ROWS($1:1)<=COUNTIF(B$2:B$8,"vac") =IF(ISERROR(SMALL(IF(B$2:B$8="vac",ROW(A$2:A$8)-ROW(A$2)+1),ROWS($1:1))) With the error trap I've used the formula only has to process the data once. The logic is that you count the number of instances that meet the criteria: COUNTIF(B$2:B$8,"vac") Then compare that to the number of cells that the formula is being copied to: ROWS($1:1) When you drag copy down to more cells the ROWS($1:1) function will increment to $1:2, $1:3 etc. This is compared to COUNTIF(B$2:B$8,"vac") and based on your posted example, COUNTIF(B$2:B$8,"vac") = 2. So, we end up with this: =IF(1<=2,value_if_true,value_if_false) =IF(2<=2,value_if_true,value_if_false) =IF(3<=2,value_if_true,value_if_false) etc The value_if_true argument is: INDEX(A$2:A$8,SMALL(IF(B$2:B$8="vac",ROW(A$2:A$8)-ROW(A$2)+1),ROWS($1:1)))) The value_if_false argument is: "" Returns a blank cell instead of an error, #NUM! Now, let's see what's happening when the value_if_true argument is met. INDEX(A$2:A$8,SMALL(IF(B$2:B$8="vac",ROW(A$2:A$8)-ROW(A$2)+1),ROWS($1:1)))) Ok, there's an indexed range of values, INDEX(A$2:A$8, which are the dates. There are a total of 7 elements in the range A$2:A$8. The Index function holds these elements in a relative order. That order is the total number of elements. There are 7 elements so the order is 1,2,3,4,5,6,7 whe A2 = 1 A3 = 2 A4 = 3 ... A8 = 7 Now we need to tell the formula which elements of that range to return based on meeting the criteria. That criteria is: IF(B$2:B$8="vac" This will return an array of TRUE's or FALSE's. Based on the posted example that would be: FALSE FALSE TRUE FALSE FALSE TRUE FALSE Ok, the value_if_true argument is: ROW(A$2:A$8)-ROW(A$2)+1 And the value_if_false argument is nothing. No value_if_false argument was defined and when that happens the default return is FALSE. We'll see how that comes into play later on. Back to the value_if_true argument: ROW(A$2:A$8)-ROW(A$2)+1 Since the INDEX function has a total of 7 elements indexed (1,2,3,4,5,6,7), we need a means of generating an array of numbers from 1 to 7 that correspond to the indexed elements. That's where ROW comes in handy. ROW(A$2:A$8) generates an array of 7 numbers but that array is 2,3,4,5,6,7,8 and that array does not correspond to the indexed array of 1,2,3,4,5,6,7. To take care of that we subtract the offset then add 1: -ROW(A$2)+1 This is how that is processed in the formula: 2 - 2 +1 = 1 3 - 2 + 1 =2 4 - 2 + 1 =3 5 - 2 + 1 =4 ... 8 - 2 + 1 =7 Now we have our array from 1 to 7 that correspond to the indexed array of 1 to 7. There are other ways to generate that array but this is the most "foolproof". So, now we put this all together to generate yet another array: If TRUE = ROW number, if FALSE = FALSE: B2 = vac = FALSE = FALSE B3 = vac = FALSE = FALSE B4 = vac = TRUE = 3 B5 = vac = FALSE = FALSE B6 = vac = FALSE = FALSE B7 = vac = TRUE = 6 B8 = vac = FALSE = FALSE That array is then passed to the SMALL function: SMALL({F,F,3,F,F,6,F},ROWS($1:1)) As is, that evaluates to the first smallest value which is 3. When drag copied down the ROWS function will increment to $1:2 for the second smallest, $1:3 for the third smallest, etc. Since there is no third smallest that would generate a #NUM! error but remember, we have that taken care of using our "psuedo" error trap. Putting it all together. When copied down this is what you get: INDEX(A$2:A$8,3) = the 3 rd element of the indexed array = 1/4 INDEX(A$2:A$8,6) = the 6 th element of the indexed array = 1/7 INDEX(A$2:A$8,#NUM!) = "" (blank) There you have it! Biff |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
extract matching vales
You're welcome. Thanks for the positive feedback!
Biff "TUNGANA KURMA RAJU" wrote in message ... Biff, What a marvellous explanation and logic.Really you are great .Simply watching the formula is never reflects depth of logic behind it.As you said these things can't be taught in classes.No books have this day to day practical solutions.Thank you. "Biff" wrote: "TUNGANA KURMA RAJU" wrote... Hi,Biff ,Thanks it worked great(robust version).I would like to understand your formula in depth.Can you please explain me step by step Sure! Here is an explanation I wrote for another poster. Both formulas (the one you're using and the one in this explanation) work exactly the same way. The only difference is the logical test. In your formula that test is: IF(LEFT(A$2:A$1200,3)="raj". In this explanation that test is: IF(B$2:B$8="vac". So, wherever you see mention of IF(B$2:B$8="vac", you can just substitute your logical test. ******************************** =IF(ROWS($1:1)<=COUNTIF(B$2:B$8,"vac"),INDEX(A$2:A $8,SMALL(IF(B$2:B$8="vac",ROW(A$2:A$8)-ROW(A$2)+1),ROWS($1:1))),"") The only part of the formula that you actually need is this: INDEX(A$2:A$8,SMALL(IF(B$2:B$8="vac",ROW(A$2:A$8)-ROW(A$2)+1),ROWS($1:1))) However, if you drag copy down, once the data that meets the criteria is exhausted the next cell(s) will return #NUM! errors. Errors are unsightly and can cause problems in any downstream calculations. We can build an error trap in the formula that catches these errors so that they're not displayed and won't affect any downstream calculations. Excel has some error testing functions like : Iserror, Isna, Error.Type. Using the Iserror function to test for errors and "trap" them, the formula would look like this: =IF(ISERROR(SMALL(IF(B$2:B$8="vac",ROW(A$2:A$8)-ROW(A$2)+1),ROWS($1:1))),"",INDEX(A$2:A$8,SMALL(IF (B$2:B$8="vac",ROW(A$2:A$8)-ROW(A$2)+1),ROWS($1:1)))) As you can see, this makes the formula about twice as long and, if I was still using the Sheet references, this would make it even longer! Long formulas tend to "scare" people! Not only is the formula long but when the error trap evaluates to FALSE (no error) the formula has to process the data twice. So naturally, that takes twice as long. I used a "psuedo" error trap that effectively does the same thing but is much shorter to express and is more efficient: =IF(ROWS($1:1)<=COUNTIF(B$2:B$8,"vac") =IF(ISERROR(SMALL(IF(B$2:B$8="vac",ROW(A$2:A$8)-ROW(A$2)+1),ROWS($1:1))) With the error trap I've used the formula only has to process the data once. The logic is that you count the number of instances that meet the criteria: COUNTIF(B$2:B$8,"vac") Then compare that to the number of cells that the formula is being copied to: ROWS($1:1) When you drag copy down to more cells the ROWS($1:1) function will increment to $1:2, $1:3 etc. This is compared to COUNTIF(B$2:B$8,"vac") and based on your posted example, COUNTIF(B$2:B$8,"vac") = 2. So, we end up with this: =IF(1<=2,value_if_true,value_if_false) =IF(2<=2,value_if_true,value_if_false) =IF(3<=2,value_if_true,value_if_false) etc The value_if_true argument is: INDEX(A$2:A$8,SMALL(IF(B$2:B$8="vac",ROW(A$2:A$8)-ROW(A$2)+1),ROWS($1:1)))) The value_if_false argument is: "" Returns a blank cell instead of an error, #NUM! Now, let's see what's happening when the value_if_true argument is met. INDEX(A$2:A$8,SMALL(IF(B$2:B$8="vac",ROW(A$2:A$8)-ROW(A$2)+1),ROWS($1:1)))) Ok, there's an indexed range of values, INDEX(A$2:A$8, which are the dates. There are a total of 7 elements in the range A$2:A$8. The Index function holds these elements in a relative order. That order is the total number of elements. There are 7 elements so the order is 1,2,3,4,5,6,7 whe A2 = 1 A3 = 2 A4 = 3 ... A8 = 7 Now we need to tell the formula which elements of that range to return based on meeting the criteria. That criteria is: IF(B$2:B$8="vac" This will return an array of TRUE's or FALSE's. Based on the posted example that would be: FALSE FALSE TRUE FALSE FALSE TRUE FALSE Ok, the value_if_true argument is: ROW(A$2:A$8)-ROW(A$2)+1 And the value_if_false argument is nothing. No value_if_false argument was defined and when that happens the default return is FALSE. We'll see how that comes into play later on. Back to the value_if_true argument: ROW(A$2:A$8)-ROW(A$2)+1 Since the INDEX function has a total of 7 elements indexed (1,2,3,4,5,6,7), we need a means of generating an array of numbers from 1 to 7 that correspond to the indexed elements. That's where ROW comes in handy. ROW(A$2:A$8) generates an array of 7 numbers but that array is 2,3,4,5,6,7,8 and that array does not correspond to the indexed array of 1,2,3,4,5,6,7. To take care of that we subtract the offset then add 1: -ROW(A$2)+1 This is how that is processed in the formula: 2 - 2 +1 = 1 3 - 2 + 1 =2 4 - 2 + 1 =3 5 - 2 + 1 =4 ... 8 - 2 + 1 =7 Now we have our array from 1 to 7 that correspond to the indexed array of 1 to 7. There are other ways to generate that array but this is the most "foolproof". So, now we put this all together to generate yet another array: If TRUE = ROW number, if FALSE = FALSE: B2 = vac = FALSE = FALSE B3 = vac = FALSE = FALSE B4 = vac = TRUE = 3 B5 = vac = FALSE = FALSE B6 = vac = FALSE = FALSE B7 = vac = TRUE = 6 B8 = vac = FALSE = FALSE That array is then passed to the SMALL function: SMALL({F,F,3,F,F,6,F},ROWS($1:1)) As is, that evaluates to the first smallest value which is 3. When drag copied down the ROWS function will increment to $1:2 for the second smallest, $1:3 for the third smallest, etc. Since there is no third smallest that would generate a #NUM! error but remember, we have that taken care of using our "psuedo" error trap. Putting it all together. When copied down this is what you get: INDEX(A$2:A$8,3) = the 3 rd element of the indexed array = 1/4 INDEX(A$2:A$8,6) = the 6 th element of the indexed array = 1/7 INDEX(A$2:A$8,#NUM!) = "" (blank) There you have it! Biff |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
extract matching vales
Biff,
I think i got the theory, I just cannot translate the formula to named ranges. Can you assist. Thanks "Biff" wrote: "TUNGANA KURMA RAJU" wrote... Hi,Biff ,Thanks it worked great(robust version).I would like to understand your formula in depth.Can you please explain me step by step Sure! Here is an explanation I wrote for another poster. Both formulas (the one you're using and the one in this explanation) work exactly the same way. The only difference is the logical test. In your formula that test is: IF(LEFT(A$2:A$1200,3)="raj". In this explanation that test is: IF(B$2:B$8="vac". So, wherever you see mention of IF(B$2:B$8="vac", you can just substitute your logical test. ******************************** =IF(ROWS($1:1)<=COUNTIF(B$2:B$8,"vac"),INDEX(A$2:A $8,SMALL(IF(B$2:B$8="vac",ROW(A$2:A$8)-ROW(A$2)+1),ROWS($1:1))),"") The only part of the formula that you actually need is this: INDEX(A$2:A$8,SMALL(IF(B$2:B$8="vac",ROW(A$2:A$8)-ROW(A$2)+1),ROWS($1:1))) However, if you drag copy down, once the data that meets the criteria is exhausted the next cell(s) will return #NUM! errors. Errors are unsightly and can cause problems in any downstream calculations. We can build an error trap in the formula that catches these errors so that they're not displayed and won't affect any downstream calculations. Excel has some error testing functions like : Iserror, Isna, Error.Type. Using the Iserror function to test for errors and "trap" them, the formula would look like this: =IF(ISERROR(SMALL(IF(B$2:B$8="vac",ROW(A$2:A$8)-ROW(A$2)+1),ROWS($1:1))),"",INDEX(A$2:A$8,SMALL(IF (B$2:B$8="vac",ROW(A$2:A$8)-ROW(A$2)+1),ROWS($1:1)))) As you can see, this makes the formula about twice as long and, if I was still using the Sheet references, this would make it even longer! Long formulas tend to "scare" people! Not only is the formula long but when the error trap evaluates to FALSE (no error) the formula has to process the data twice. So naturally, that takes twice as long. I used a "psuedo" error trap that effectively does the same thing but is much shorter to express and is more efficient: =IF(ROWS($1:1)<=COUNTIF(B$2:B$8,"vac") =IF(ISERROR(SMALL(IF(B$2:B$8="vac",ROW(A$2:A$8)-ROW(A$2)+1),ROWS($1:1))) With the error trap I've used the formula only has to process the data once. The logic is that you count the number of instances that meet the criteria: COUNTIF(B$2:B$8,"vac") Then compare that to the number of cells that the formula is being copied to: ROWS($1:1) When you drag copy down to more cells the ROWS($1:1) function will increment to $1:2, $1:3 etc. This is compared to COUNTIF(B$2:B$8,"vac") and based on your posted example, COUNTIF(B$2:B$8,"vac") = 2. So, we end up with this: =IF(1<=2,value_if_true,value_if_false) =IF(2<=2,value_if_true,value_if_false) =IF(3<=2,value_if_true,value_if_false) etc The value_if_true argument is: INDEX(A$2:A$8,SMALL(IF(B$2:B$8="vac",ROW(A$2:A$8)-ROW(A$2)+1),ROWS($1:1)))) The value_if_false argument is: "" Returns a blank cell instead of an error, #NUM! Now, let's see what's happening when the value_if_true argument is met. INDEX(A$2:A$8,SMALL(IF(B$2:B$8="vac",ROW(A$2:A$8)-ROW(A$2)+1),ROWS($1:1)))) Ok, there's an indexed range of values, INDEX(A$2:A$8, which are the dates. There are a total of 7 elements in the range A$2:A$8. The Index function holds these elements in a relative order. That order is the total number of elements. There are 7 elements so the order is 1,2,3,4,5,6,7 whe A2 = 1 A3 = 2 A4 = 3 ... A8 = 7 Now we need to tell the formula which elements of that range to return based on meeting the criteria. That criteria is: IF(B$2:B$8="vac" This will return an array of TRUE's or FALSE's. Based on the posted example that would be: FALSE FALSE TRUE FALSE FALSE TRUE FALSE Ok, the value_if_true argument is: ROW(A$2:A$8)-ROW(A$2)+1 And the value_if_false argument is nothing. No value_if_false argument was defined and when that happens the default return is FALSE. We'll see how that comes into play later on. Back to the value_if_true argument: ROW(A$2:A$8)-ROW(A$2)+1 Since the INDEX function has a total of 7 elements indexed (1,2,3,4,5,6,7), we need a means of generating an array of numbers from 1 to 7 that correspond to the indexed elements. That's where ROW comes in handy. ROW(A$2:A$8) generates an array of 7 numbers but that array is 2,3,4,5,6,7,8 and that array does not correspond to the indexed array of 1,2,3,4,5,6,7. To take care of that we subtract the offset then add 1: -ROW(A$2)+1 This is how that is processed in the formula: 2 - 2 +1 = 1 3 - 2 + 1 =2 4 - 2 + 1 =3 5 - 2 + 1 =4 ... 8 - 2 + 1 =7 Now we have our array from 1 to 7 that correspond to the indexed array of 1 to 7. There are other ways to generate that array but this is the most "foolproof". So, now we put this all together to generate yet another array: If TRUE = ROW number, if FALSE = FALSE: B2 = vac = FALSE = FALSE B3 = vac = FALSE = FALSE B4 = vac = TRUE = 3 B5 = vac = FALSE = FALSE B6 = vac = FALSE = FALSE B7 = vac = TRUE = 6 B8 = vac = FALSE = FALSE That array is then passed to the SMALL function: SMALL({F,F,3,F,F,6,F},ROWS($1:1)) As is, that evaluates to the first smallest value which is 3. When drag copied down the ROWS function will increment to $1:2 for the second smallest, $1:3 for the third smallest, etc. Since there is no third smallest that would generate a #NUM! error but remember, we have that taken care of using our "psuedo" error trap. Putting it all together. When copied down this is what you get: INDEX(A$2:A$8,3) = the 3 rd element of the indexed array = 1/4 INDEX(A$2:A$8,6) = the 6 th element of the indexed array = 1/7 INDEX(A$2:A$8,#NUM!) = "" (blank) There you have it! Biff |
|
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
extract matching vales
Need the details but I'll take a guess that the problem is coming from this
portion: ROW(A$2:A$8)-ROW(A$2)+1) For named ranges: ROW(Named_Range)-MIN(ROW(Named_Range))+1) Biff "thesaxonuk" wrote in message ... Biff, I think i got the theory, I just cannot translate the formula to named ranges. Can you assist. Thanks "Biff" wrote: "TUNGANA KURMA RAJU" wrote... Hi,Biff ,Thanks it worked great(robust version).I would like to understand your formula in depth.Can you please explain me step by step Sure! Here is an explanation I wrote for another poster. Both formulas (the one you're using and the one in this explanation) work exactly the same way. The only difference is the logical test. In your formula that test is: IF(LEFT(A$2:A$1200,3)="raj". In this explanation that test is: IF(B$2:B$8="vac". So, wherever you see mention of IF(B$2:B$8="vac", you can just substitute your logical test. ******************************** =IF(ROWS($1:1)<=COUNTIF(B$2:B$8,"vac"),INDEX(A$2:A $8,SMALL(IF(B$2:B$8="vac",ROW(A$2:A$8)-ROW(A$2)+1),ROWS($1:1))),"") The only part of the formula that you actually need is this: INDEX(A$2:A$8,SMALL(IF(B$2:B$8="vac",ROW(A$2:A$8)-ROW(A$2)+1),ROWS($1:1))) However, if you drag copy down, once the data that meets the criteria is exhausted the next cell(s) will return #NUM! errors. Errors are unsightly and can cause problems in any downstream calculations. We can build an error trap in the formula that catches these errors so that they're not displayed and won't affect any downstream calculations. Excel has some error testing functions like : Iserror, Isna, Error.Type. Using the Iserror function to test for errors and "trap" them, the formula would look like this: =IF(ISERROR(SMALL(IF(B$2:B$8="vac",ROW(A$2:A$8)-ROW(A$2)+1),ROWS($1:1))),"",INDEX(A$2:A$8,SMALL(IF (B$2:B$8="vac",ROW(A$2:A$8)-ROW(A$2)+1),ROWS($1:1)))) As you can see, this makes the formula about twice as long and, if I was still using the Sheet references, this would make it even longer! Long formulas tend to "scare" people! Not only is the formula long but when the error trap evaluates to FALSE (no error) the formula has to process the data twice. So naturally, that takes twice as long. I used a "psuedo" error trap that effectively does the same thing but is much shorter to express and is more efficient: =IF(ROWS($1:1)<=COUNTIF(B$2:B$8,"vac") =IF(ISERROR(SMALL(IF(B$2:B$8="vac",ROW(A$2:A$8)-ROW(A$2)+1),ROWS($1:1))) With the error trap I've used the formula only has to process the data once. The logic is that you count the number of instances that meet the criteria: COUNTIF(B$2:B$8,"vac") Then compare that to the number of cells that the formula is being copied to: ROWS($1:1) When you drag copy down to more cells the ROWS($1:1) function will increment to $1:2, $1:3 etc. This is compared to COUNTIF(B$2:B$8,"vac") and based on your posted example, COUNTIF(B$2:B$8,"vac") = 2. So, we end up with this: =IF(1<=2,value_if_true,value_if_false) =IF(2<=2,value_if_true,value_if_false) =IF(3<=2,value_if_true,value_if_false) etc The value_if_true argument is: INDEX(A$2:A$8,SMALL(IF(B$2:B$8="vac",ROW(A$2:A$8)-ROW(A$2)+1),ROWS($1:1)))) The value_if_false argument is: "" Returns a blank cell instead of an error, #NUM! Now, let's see what's happening when the value_if_true argument is met. INDEX(A$2:A$8,SMALL(IF(B$2:B$8="vac",ROW(A$2:A$8)-ROW(A$2)+1),ROWS($1:1)))) Ok, there's an indexed range of values, INDEX(A$2:A$8, which are the dates. There are a total of 7 elements in the range A$2:A$8. The Index function holds these elements in a relative order. That order is the total number of elements. There are 7 elements so the order is 1,2,3,4,5,6,7 whe A2 = 1 A3 = 2 A4 = 3 ... A8 = 7 Now we need to tell the formula which elements of that range to return based on meeting the criteria. That criteria is: IF(B$2:B$8="vac" This will return an array of TRUE's or FALSE's. Based on the posted example that would be: FALSE FALSE TRUE FALSE FALSE TRUE FALSE Ok, the value_if_true argument is: ROW(A$2:A$8)-ROW(A$2)+1 And the value_if_false argument is nothing. No value_if_false argument was defined and when that happens the default return is FALSE. We'll see how that comes into play later on. Back to the value_if_true argument: ROW(A$2:A$8)-ROW(A$2)+1 Since the INDEX function has a total of 7 elements indexed (1,2,3,4,5,6,7), we need a means of generating an array of numbers from 1 to 7 that correspond to the indexed elements. That's where ROW comes in handy. ROW(A$2:A$8) generates an array of 7 numbers but that array is 2,3,4,5,6,7,8 and that array does not correspond to the indexed array of 1,2,3,4,5,6,7. To take care of that we subtract the offset then add 1: -ROW(A$2)+1 This is how that is processed in the formula: 2 - 2 +1 = 1 3 - 2 + 1 =2 4 - 2 + 1 =3 5 - 2 + 1 =4 ... 8 - 2 + 1 =7 Now we have our array from 1 to 7 that correspond to the indexed array of 1 to 7. There are other ways to generate that array but this is the most "foolproof". So, now we put this all together to generate yet another array: If TRUE = ROW number, if FALSE = FALSE: B2 = vac = FALSE = FALSE B3 = vac = FALSE = FALSE B4 = vac = TRUE = 3 B5 = vac = FALSE = FALSE B6 = vac = FALSE = FALSE B7 = vac = TRUE = 6 B8 = vac = FALSE = FALSE That array is then passed to the SMALL function: SMALL({F,F,3,F,F,6,F},ROWS($1:1)) As is, that evaluates to the first smallest value which is 3. When drag copied down the ROWS function will increment to $1:2 for the second smallest, $1:3 for the third smallest, etc. Since there is no third smallest that would generate a #NUM! error but remember, we have that taken care of using our "psuedo" error trap. Putting it all together. When copied down this is what you get: INDEX(A$2:A$8,3) = the 3 rd element of the indexed array = 1/4 INDEX(A$2:A$8,6) = the 6 th element of the indexed array = 1/7 INDEX(A$2:A$8,#NUM!) = "" (blank) There you have it! Biff |
|
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
extract matching vales
I have two workbooks.
In workbook 1 there are dynamic named ranges for : Ref no spaces in list & unique Package Ref does have spaces in list & Duplicates is the same as Ref for master Ref Name No spaces in list but has duplicates but not within the same package ref Type Does have spaces in list and duplicates In workbook 2 i have the criteria fields of: Ref - field C1 Type - field C27 Count of Refs within Package Ref - field B34 The formula starts at line 37 I have already established how many Ref's there are within a Package and this figure can range from 0 to 25 What I need by use of the formula below is: Using the Ref and Type as selection criteria the name and ref to be displayed for the repective number of rows down hope this makes sense. "Biff" wrote: Need the details but I'll take a guess that the problem is coming from this portion: ROW(A$2:A$8)-ROW(A$2)+1) For named ranges: ROW(Named_Range)-MIN(ROW(Named_Range))+1) Biff "thesaxonuk" wrote in message ... Biff, I think i got the theory, I just cannot translate the formula to named ranges. Can you assist. Thanks "Biff" wrote: "TUNGANA KURMA RAJU" wrote... Hi,Biff ,Thanks it worked great(robust version).I would like to understand your formula in depth.Can you please explain me step by step Sure! Here is an explanation I wrote for another poster. Both formulas (the one you're using and the one in this explanation) work exactly the same way. The only difference is the logical test. In your formula that test is: IF(LEFT(A$2:A$1200,3)="raj". In this explanation that test is: IF(B$2:B$8="vac". So, wherever you see mention of IF(B$2:B$8="vac", you can just substitute your logical test. ******************************** =IF(ROWS($1:1)<=COUNTIF(B$2:B$8,"vac"),INDEX(A$2:A $8,SMALL(IF(B$2:B$8="vac",ROW(A$2:A$8)-ROW(A$2)+1),ROWS($1:1))),"") The only part of the formula that you actually need is this: INDEX(A$2:A$8,SMALL(IF(B$2:B$8="vac",ROW(A$2:A$8)-ROW(A$2)+1),ROWS($1:1))) However, if you drag copy down, once the data that meets the criteria is exhausted the next cell(s) will return #NUM! errors. Errors are unsightly and can cause problems in any downstream calculations. We can build an error trap in the formula that catches these errors so that they're not displayed and won't affect any downstream calculations. Excel has some error testing functions like : Iserror, Isna, Error.Type. Using the Iserror function to test for errors and "trap" them, the formula would look like this: =IF(ISERROR(SMALL(IF(B$2:B$8="vac",ROW(A$2:A$8)-ROW(A$2)+1),ROWS($1:1))),"",INDEX(A$2:A$8,SMALL(IF (B$2:B$8="vac",ROW(A$2:A$8)-ROW(A$2)+1),ROWS($1:1)))) As you can see, this makes the formula about twice as long and, if I was still using the Sheet references, this would make it even longer! Long formulas tend to "scare" people! Not only is the formula long but when the error trap evaluates to FALSE (no error) the formula has to process the data twice. So naturally, that takes twice as long. I used a "psuedo" error trap that effectively does the same thing but is much shorter to express and is more efficient: =IF(ROWS($1:1)<=COUNTIF(B$2:B$8,"vac") =IF(ISERROR(SMALL(IF(B$2:B$8="vac",ROW(A$2:A$8)-ROW(A$2)+1),ROWS($1:1))) With the error trap I've used the formula only has to process the data once. The logic is that you count the number of instances that meet the criteria: COUNTIF(B$2:B$8,"vac") Then compare that to the number of cells that the formula is being copied to: ROWS($1:1) When you drag copy down to more cells the ROWS($1:1) function will increment to $1:2, $1:3 etc. This is compared to COUNTIF(B$2:B$8,"vac") and based on your posted example, COUNTIF(B$2:B$8,"vac") = 2. So, we end up with this: =IF(1<=2,value_if_true,value_if_false) =IF(2<=2,value_if_true,value_if_false) =IF(3<=2,value_if_true,value_if_false) etc The value_if_true argument is: INDEX(A$2:A$8,SMALL(IF(B$2:B$8="vac",ROW(A$2:A$8)-ROW(A$2)+1),ROWS($1:1)))) The value_if_false argument is: "" Returns a blank cell instead of an error, #NUM! Now, let's see what's happening when the value_if_true argument is met. INDEX(A$2:A$8,SMALL(IF(B$2:B$8="vac",ROW(A$2:A$8)-ROW(A$2)+1),ROWS($1:1)))) Ok, there's an indexed range of values, INDEX(A$2:A$8, which are the dates. There are a total of 7 elements in the range A$2:A$8. The Index function holds these elements in a relative order. That order is the total number of elements. There are 7 elements so the order is 1,2,3,4,5,6,7 whe A2 = 1 A3 = 2 A4 = 3 ... A8 = 7 Now we need to tell the formula which elements of that range to return based on meeting the criteria. That criteria is: IF(B$2:B$8="vac" This will return an array of TRUE's or FALSE's. Based on the posted example that would be: FALSE FALSE TRUE FALSE FALSE TRUE FALSE Ok, the value_if_true argument is: ROW(A$2:A$8)-ROW(A$2)+1 And the value_if_false argument is nothing. No value_if_false argument was defined and when that happens the default return is FALSE. We'll see how that comes into play later on. Back to the value_if_true argument: ROW(A$2:A$8)-ROW(A$2)+1 Since the INDEX function has a total of 7 elements indexed (1,2,3,4,5,6,7), we need a means of generating an array of numbers from 1 to 7 that correspond to the indexed elements. That's where ROW comes in handy. ROW(A$2:A$8) generates an array of 7 numbers but that array is 2,3,4,5,6,7,8 and that array does not correspond to the indexed array of 1,2,3,4,5,6,7. To take care of that we subtract the offset then add 1: -ROW(A$2)+1 This is how that is processed in the formula: 2 - 2 +1 = 1 3 - 2 + 1 =2 4 - 2 + 1 =3 5 - 2 + 1 =4 ... 8 - 2 + 1 =7 Now we have our array from 1 to 7 that correspond to the indexed array of 1 to 7. There are other ways to generate that array but this is the most "foolproof". So, now we put this all together to generate yet another array: If TRUE = ROW number, if FALSE = FALSE: B2 = vac = FALSE = FALSE B3 = vac = FALSE = FALSE B4 = vac = TRUE = 3 B5 = vac = FALSE = FALSE B6 = vac = FALSE = FALSE B7 = vac = TRUE = 6 B8 = vac = FALSE = FALSE That array is then passed to the SMALL function: SMALL({F,F,3,F,F,6,F},ROWS($1:1)) As is, that evaluates to the first smallest value which is 3. When drag copied down the ROWS function will increment to $1:2 for the second smallest, $1:3 for the third smallest, etc. Since there is no third smallest that would generate a #NUM! error but remember, we have that taken care of using our "psuedo" error trap. Putting it all together. When copied down this is what you get: INDEX(A$2:A$8,3) = the 3 rd element of the indexed array = 1/4 INDEX(A$2:A$8,6) = the 6 th element of the indexed array = 1/7 INDEX(A$2:A$8,#NUM!) = "" (blank) There you have it! Biff |
|
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
extract matching vales
In workbook 1 there are dynamic named ranges
If you have dynamic ranges that use the OFFSET function then they won't work in other calling workbooks unless the source workbook is open. Biff "thesaxonuk" wrote in message ... I have two workbooks. In workbook 1 there are dynamic named ranges for : Ref no spaces in list & unique Package Ref does have spaces in list & Duplicates is the same as Ref for master Ref Name No spaces in list but has duplicates but not within the same package ref Type Does have spaces in list and duplicates In workbook 2 i have the criteria fields of: Ref - field C1 Type - field C27 Count of Refs within Package Ref - field B34 The formula starts at line 37 I have already established how many Ref's there are within a Package and this figure can range from 0 to 25 What I need by use of the formula below is: Using the Ref and Type as selection criteria the name and ref to be displayed for the repective number of rows down hope this makes sense. "Biff" wrote: Need the details but I'll take a guess that the problem is coming from this portion: ROW(A$2:A$8)-ROW(A$2)+1) For named ranges: ROW(Named_Range)-MIN(ROW(Named_Range))+1) Biff "thesaxonuk" wrote in message ... Biff, I think i got the theory, I just cannot translate the formula to named ranges. Can you assist. Thanks "Biff" wrote: "TUNGANA KURMA RAJU" wrote... Hi,Biff ,Thanks it worked great(robust version).I would like to understand your formula in depth.Can you please explain me step by step Sure! Here is an explanation I wrote for another poster. Both formulas (the one you're using and the one in this explanation) work exactly the same way. The only difference is the logical test. In your formula that test is: IF(LEFT(A$2:A$1200,3)="raj". In this explanation that test is: IF(B$2:B$8="vac". So, wherever you see mention of IF(B$2:B$8="vac", you can just substitute your logical test. ******************************** =IF(ROWS($1:1)<=COUNTIF(B$2:B$8,"vac"),INDEX(A$2:A $8,SMALL(IF(B$2:B$8="vac",ROW(A$2:A$8)-ROW(A$2)+1),ROWS($1:1))),"") The only part of the formula that you actually need is this: INDEX(A$2:A$8,SMALL(IF(B$2:B$8="vac",ROW(A$2:A$8)-ROW(A$2)+1),ROWS($1:1))) However, if you drag copy down, once the data that meets the criteria is exhausted the next cell(s) will return #NUM! errors. Errors are unsightly and can cause problems in any downstream calculations. We can build an error trap in the formula that catches these errors so that they're not displayed and won't affect any downstream calculations. Excel has some error testing functions like : Iserror, Isna, Error.Type. Using the Iserror function to test for errors and "trap" them, the formula would look like this: =IF(ISERROR(SMALL(IF(B$2:B$8="vac",ROW(A$2:A$8)-ROW(A$2)+1),ROWS($1:1))),"",INDEX(A$2:A$8,SMALL(IF (B$2:B$8="vac",ROW(A$2:A$8)-ROW(A$2)+1),ROWS($1:1)))) As you can see, this makes the formula about twice as long and, if I was still using the Sheet references, this would make it even longer! Long formulas tend to "scare" people! Not only is the formula long but when the error trap evaluates to FALSE (no error) the formula has to process the data twice. So naturally, that takes twice as long. I used a "psuedo" error trap that effectively does the same thing but is much shorter to express and is more efficient: =IF(ROWS($1:1)<=COUNTIF(B$2:B$8,"vac") =IF(ISERROR(SMALL(IF(B$2:B$8="vac",ROW(A$2:A$8)-ROW(A$2)+1),ROWS($1:1))) With the error trap I've used the formula only has to process the data once. The logic is that you count the number of instances that meet the criteria: COUNTIF(B$2:B$8,"vac") Then compare that to the number of cells that the formula is being copied to: ROWS($1:1) When you drag copy down to more cells the ROWS($1:1) function will increment to $1:2, $1:3 etc. This is compared to COUNTIF(B$2:B$8,"vac") and based on your posted example, COUNTIF(B$2:B$8,"vac") = 2. So, we end up with this: =IF(1<=2,value_if_true,value_if_false) =IF(2<=2,value_if_true,value_if_false) =IF(3<=2,value_if_true,value_if_false) etc The value_if_true argument is: INDEX(A$2:A$8,SMALL(IF(B$2:B$8="vac",ROW(A$2:A$8)-ROW(A$2)+1),ROWS($1:1)))) The value_if_false argument is: "" Returns a blank cell instead of an error, #NUM! Now, let's see what's happening when the value_if_true argument is met. INDEX(A$2:A$8,SMALL(IF(B$2:B$8="vac",ROW(A$2:A$8)-ROW(A$2)+1),ROWS($1:1)))) Ok, there's an indexed range of values, INDEX(A$2:A$8, which are the dates. There are a total of 7 elements in the range A$2:A$8. The Index function holds these elements in a relative order. That order is the total number of elements. There are 7 elements so the order is 1,2,3,4,5,6,7 whe A2 = 1 A3 = 2 A4 = 3 ... A8 = 7 Now we need to tell the formula which elements of that range to return based on meeting the criteria. That criteria is: IF(B$2:B$8="vac" This will return an array of TRUE's or FALSE's. Based on the posted example that would be: FALSE FALSE TRUE FALSE FALSE TRUE FALSE Ok, the value_if_true argument is: ROW(A$2:A$8)-ROW(A$2)+1 And the value_if_false argument is nothing. No value_if_false argument was defined and when that happens the default return is FALSE. We'll see how that comes into play later on. Back to the value_if_true argument: ROW(A$2:A$8)-ROW(A$2)+1 Since the INDEX function has a total of 7 elements indexed (1,2,3,4,5,6,7), we need a means of generating an array of numbers from 1 to 7 that correspond to the indexed elements. That's where ROW comes in handy. ROW(A$2:A$8) generates an array of 7 numbers but that array is 2,3,4,5,6,7,8 and that array does not correspond to the indexed array of 1,2,3,4,5,6,7. To take care of that we subtract the offset then add 1: -ROW(A$2)+1 This is how that is processed in the formula: 2 - 2 +1 = 1 3 - 2 + 1 =2 4 - 2 + 1 =3 5 - 2 + 1 =4 ... 8 - 2 + 1 =7 Now we have our array from 1 to 7 that correspond to the indexed array of 1 to 7. There are other ways to generate that array but this is the most "foolproof". So, now we put this all together to generate yet another array: If TRUE = ROW number, if FALSE = FALSE: B2 = vac = FALSE = FALSE B3 = vac = FALSE = FALSE B4 = vac = TRUE = 3 B5 = vac = FALSE = FALSE B6 = vac = FALSE = FALSE B7 = vac = TRUE = 6 B8 = vac = FALSE = FALSE That array is then passed to the SMALL function: SMALL({F,F,3,F,F,6,F},ROWS($1:1)) As is, that evaluates to the first smallest value which is 3. When drag copied down the ROWS function will increment to $1:2 for the second smallest, $1:3 for the third smallest, etc. Since there is no third smallest that would generate a #NUM! error but remember, we have that taken care of using our "psuedo" error trap. Putting it all together. When copied down this is what you get: INDEX(A$2:A$8,3) = the 3 rd element of the indexed array = 1/4 INDEX(A$2:A$8,6) = the 6 th element of the indexed array = 1/7 INDEX(A$2:A$8,#NUM!) = "" (blank) There you have it! Biff |
|
#15
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
extract matching vales
Biff,
Thanks, I managed to get it to work eventually. "Biff" wrote: In workbook 1 there are dynamic named ranges If you have dynamic ranges that use the OFFSET function then they won't work in other calling workbooks unless the source workbook is open. Biff "thesaxonuk" wrote in message ... I have two workbooks. In workbook 1 there are dynamic named ranges for : Ref no spaces in list & unique Package Ref does have spaces in list & Duplicates is the same as Ref for master Ref Name No spaces in list but has duplicates but not within the same package ref Type Does have spaces in list and duplicates In workbook 2 i have the criteria fields of: Ref - field C1 Type - field C27 Count of Refs within Package Ref - field B34 The formula starts at line 37 I have already established how many Ref's there are within a Package and this figure can range from 0 to 25 What I need by use of the formula below is: Using the Ref and Type as selection criteria the name and ref to be displayed for the repective number of rows down hope this makes sense. "Biff" wrote: Need the details but I'll take a guess that the problem is coming from this portion: ROW(A$2:A$8)-ROW(A$2)+1) For named ranges: ROW(Named_Range)-MIN(ROW(Named_Range))+1) Biff "thesaxonuk" wrote in message ... Biff, I think i got the theory, I just cannot translate the formula to named ranges. Can you assist. Thanks "Biff" wrote: "TUNGANA KURMA RAJU" wrote... Hi,Biff ,Thanks it worked great(robust version).I would like to understand your formula in depth.Can you please explain me step by step Sure! Here is an explanation I wrote for another poster. Both formulas (the one you're using and the one in this explanation) work exactly the same way. The only difference is the logical test. In your formula that test is: IF(LEFT(A$2:A$1200,3)="raj". In this explanation that test is: IF(B$2:B$8="vac". So, wherever you see mention of IF(B$2:B$8="vac", you can just substitute your logical test. ******************************** =IF(ROWS($1:1)<=COUNTIF(B$2:B$8,"vac"),INDEX(A$2:A $8,SMALL(IF(B$2:B$8="vac",ROW(A$2:A$8)-ROW(A$2)+1),ROWS($1:1))),"") The only part of the formula that you actually need is this: INDEX(A$2:A$8,SMALL(IF(B$2:B$8="vac",ROW(A$2:A$8)-ROW(A$2)+1),ROWS($1:1))) However, if you drag copy down, once the data that meets the criteria is exhausted the next cell(s) will return #NUM! errors. Errors are unsightly and can cause problems in any downstream calculations. We can build an error trap in the formula that catches these errors so that they're not displayed and won't affect any downstream calculations. Excel has some error testing functions like : Iserror, Isna, Error.Type. Using the Iserror function to test for errors and "trap" them, the formula would look like this: =IF(ISERROR(SMALL(IF(B$2:B$8="vac",ROW(A$2:A$8)-ROW(A$2)+1),ROWS($1:1))),"",INDEX(A$2:A$8,SMALL(IF (B$2:B$8="vac",ROW(A$2:A$8)-ROW(A$2)+1),ROWS($1:1)))) As you can see, this makes the formula about twice as long and, if I was still using the Sheet references, this would make it even longer! Long formulas tend to "scare" people! Not only is the formula long but when the error trap evaluates to FALSE (no error) the formula has to process the data twice. So naturally, that takes twice as long. I used a "psuedo" error trap that effectively does the same thing but is much shorter to express and is more efficient: =IF(ROWS($1:1)<=COUNTIF(B$2:B$8,"vac") =IF(ISERROR(SMALL(IF(B$2:B$8="vac",ROW(A$2:A$8)-ROW(A$2)+1),ROWS($1:1))) With the error trap I've used the formula only has to process the data once. The logic is that you count the number of instances that meet the criteria: COUNTIF(B$2:B$8,"vac") Then compare that to the number of cells that the formula is being copied to: ROWS($1:1) When you drag copy down to more cells the ROWS($1:1) function will increment to $1:2, $1:3 etc. This is compared to COUNTIF(B$2:B$8,"vac") and based on your posted example, COUNTIF(B$2:B$8,"vac") = 2. So, we end up with this: =IF(1<=2,value_if_true,value_if_false) =IF(2<=2,value_if_true,value_if_false) =IF(3<=2,value_if_true,value_if_false) etc The value_if_true argument is: INDEX(A$2:A$8,SMALL(IF(B$2:B$8="vac",ROW(A$2:A$8)-ROW(A$2)+1),ROWS($1:1)))) The value_if_false argument is: "" Returns a blank cell instead of an error, #NUM! Now, let's see what's happening when the value_if_true argument is met. INDEX(A$2:A$8,SMALL(IF(B$2:B$8="vac",ROW(A$2:A$8)-ROW(A$2)+1),ROWS($1:1)))) Ok, there's an indexed range of values, INDEX(A$2:A$8, which are the dates. There are a total of 7 elements in the range A$2:A$8. The Index function holds these elements in a relative order. That order is the total number of elements. There are 7 elements so the order is 1,2,3,4,5,6,7 whe A2 = 1 A3 = 2 A4 = 3 ... A8 = 7 Now we need to tell the formula which elements of that range to return based on meeting the criteria. That criteria is: IF(B$2:B$8="vac" This will return an array of TRUE's or FALSE's. Based on the posted example that would be: FALSE FALSE TRUE FALSE FALSE TRUE FALSE Ok, the value_if_true argument is: ROW(A$2:A$8)-ROW(A$2)+1 And the value_if_false argument is nothing. No value_if_false argument was defined and when that happens the default return is FALSE. We'll see how that comes into play later on. Back to the value_if_true argument: ROW(A$2:A$8)-ROW(A$2)+1 Since the INDEX function has a total of 7 elements indexed (1,2,3,4,5,6,7), we need a means of generating an array of numbers from 1 to 7 that correspond to the indexed elements. That's where ROW comes in handy. ROW(A$2:A$8) generates an array of 7 numbers but that array is 2,3,4,5,6,7,8 and that array does not correspond to the indexed array of 1,2,3,4,5,6,7. To take care of that we subtract the offset then add 1: -ROW(A$2)+1 This is how that is processed in the formula: 2 - 2 +1 = 1 3 - 2 + 1 =2 4 - 2 + 1 =3 5 - 2 + 1 =4 ... 8 - 2 + 1 =7 Now we have our array from 1 to 7 that correspond to the indexed array of 1 to 7. There are other ways to generate that array but this is the most "foolproof". So, now we put this all together to generate yet another array: If TRUE = ROW number, if FALSE = FALSE: B2 = vac = FALSE = FALSE B3 = vac = FALSE = FALSE B4 = vac = TRUE = 3 B5 = vac = FALSE = FALSE B6 = vac = FALSE = FALSE B7 = vac = TRUE = 6 B8 = vac = FALSE = FALSE That array is then passed to the SMALL function: SMALL({F,F,3,F,F,6,F},ROWS($1:1)) As is, that evaluates to the first smallest value which is 3. When drag copied down the ROWS function will increment to $1:2 for the second smallest, $1:3 for the third smallest, etc. Since there is no third smallest that would generate a #NUM! error but remember, we have that taken care of using our "psuedo" error trap. Putting it all together. When copied down this is what you get: INDEX(A$2:A$8,3) = the 3 rd element of the indexed array = 1/4 INDEX(A$2:A$8,6) = the 6 th element of the indexed array = 1/7 INDEX(A$2:A$8,#NUM!) = "" (blank) There you have it! Biff |
|
#16
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
extract matching vales
Good deal!
Biff "thesaxonuk" wrote in message ... Biff, Thanks, I managed to get it to work eventually. "Biff" wrote: In workbook 1 there are dynamic named ranges If you have dynamic ranges that use the OFFSET function then they won't work in other calling workbooks unless the source workbook is open. Biff "thesaxonuk" wrote in message ... I have two workbooks. In workbook 1 there are dynamic named ranges for : Ref no spaces in list & unique Package Ref does have spaces in list & Duplicates is the same as Ref for master Ref Name No spaces in list but has duplicates but not within the same package ref Type Does have spaces in list and duplicates In workbook 2 i have the criteria fields of: Ref - field C1 Type - field C27 Count of Refs within Package Ref - field B34 The formula starts at line 37 I have already established how many Ref's there are within a Package and this figure can range from 0 to 25 What I need by use of the formula below is: Using the Ref and Type as selection criteria the name and ref to be displayed for the repective number of rows down hope this makes sense. "Biff" wrote: Need the details but I'll take a guess that the problem is coming from this portion: ROW(A$2:A$8)-ROW(A$2)+1) For named ranges: ROW(Named_Range)-MIN(ROW(Named_Range))+1) Biff "thesaxonuk" wrote in message ... Biff, I think i got the theory, I just cannot translate the formula to named ranges. Can you assist. Thanks "Biff" wrote: "TUNGANA KURMA RAJU" wrote... Hi,Biff ,Thanks it worked great(robust version).I would like to understand your formula in depth.Can you please explain me step by step Sure! Here is an explanation I wrote for another poster. Both formulas (the one you're using and the one in this explanation) work exactly the same way. The only difference is the logical test. In your formula that test is: IF(LEFT(A$2:A$1200,3)="raj". In this explanation that test is: IF(B$2:B$8="vac". So, wherever you see mention of IF(B$2:B$8="vac", you can just substitute your logical test. ******************************** =IF(ROWS($1:1)<=COUNTIF(B$2:B$8,"vac"),INDEX(A$2:A $8,SMALL(IF(B$2:B$8="vac",ROW(A$2:A$8)-ROW(A$2)+1),ROWS($1:1))),"") The only part of the formula that you actually need is this: INDEX(A$2:A$8,SMALL(IF(B$2:B$8="vac",ROW(A$2:A$8)-ROW(A$2)+1),ROWS($1:1))) However, if you drag copy down, once the data that meets the criteria is exhausted the next cell(s) will return #NUM! errors. Errors are unsightly and can cause problems in any downstream calculations. We can build an error trap in the formula that catches these errors so that they're not displayed and won't affect any downstream calculations. Excel has some error testing functions like : Iserror, Isna, Error.Type. Using the Iserror function to test for errors and "trap" them, the formula would look like this: =IF(ISERROR(SMALL(IF(B$2:B$8="vac",ROW(A$2:A$8)-ROW(A$2)+1),ROWS($1:1))),"",INDEX(A$2:A$8,SMALL(IF (B$2:B$8="vac",ROW(A$2:A$8)-ROW(A$2)+1),ROWS($1:1)))) As you can see, this makes the formula about twice as long and, if I was still using the Sheet references, this would make it even longer! Long formulas tend to "scare" people! Not only is the formula long but when the error trap evaluates to FALSE (no error) the formula has to process the data twice. So naturally, that takes twice as long. I used a "psuedo" error trap that effectively does the same thing but is much shorter to express and is more efficient: =IF(ROWS($1:1)<=COUNTIF(B$2:B$8,"vac") =IF(ISERROR(SMALL(IF(B$2:B$8="vac",ROW(A$2:A$8)-ROW(A$2)+1),ROWS($1:1))) With the error trap I've used the formula only has to process the data once. The logic is that you count the number of instances that meet the criteria: COUNTIF(B$2:B$8,"vac") Then compare that to the number of cells that the formula is being copied to: ROWS($1:1) When you drag copy down to more cells the ROWS($1:1) function will increment to $1:2, $1:3 etc. This is compared to COUNTIF(B$2:B$8,"vac") and based on your posted example, COUNTIF(B$2:B$8,"vac") = 2. So, we end up with this: =IF(1<=2,value_if_true,value_if_false) =IF(2<=2,value_if_true,value_if_false) =IF(3<=2,value_if_true,value_if_false) etc The value_if_true argument is: INDEX(A$2:A$8,SMALL(IF(B$2:B$8="vac",ROW(A$2:A$8)-ROW(A$2)+1),ROWS($1:1)))) The value_if_false argument is: "" Returns a blank cell instead of an error, #NUM! Now, let's see what's happening when the value_if_true argument is met. INDEX(A$2:A$8,SMALL(IF(B$2:B$8="vac",ROW(A$2:A$8)-ROW(A$2)+1),ROWS($1:1)))) Ok, there's an indexed range of values, INDEX(A$2:A$8, which are the dates. There are a total of 7 elements in the range A$2:A$8. The Index function holds these elements in a relative order. That order is the total number of elements. There are 7 elements so the order is 1,2,3,4,5,6,7 whe A2 = 1 A3 = 2 A4 = 3 ... A8 = 7 Now we need to tell the formula which elements of that range to return based on meeting the criteria. That criteria is: IF(B$2:B$8="vac" This will return an array of TRUE's or FALSE's. Based on the posted example that would be: FALSE FALSE TRUE FALSE FALSE TRUE FALSE Ok, the value_if_true argument is: ROW(A$2:A$8)-ROW(A$2)+1 And the value_if_false argument is nothing. No value_if_false argument was defined and when that happens the default return is FALSE. We'll see how that comes into play later on. Back to the value_if_true argument: ROW(A$2:A$8)-ROW(A$2)+1 Since the INDEX function has a total of 7 elements indexed (1,2,3,4,5,6,7), we need a means of generating an array of numbers from 1 to 7 that correspond to the indexed elements. That's where ROW comes in handy. ROW(A$2:A$8) generates an array of 7 numbers but that array is 2,3,4,5,6,7,8 and that array does not correspond to the indexed array of 1,2,3,4,5,6,7. To take care of that we subtract the offset then add 1: -ROW(A$2)+1 This is how that is processed in the formula: 2 - 2 +1 = 1 3 - 2 + 1 =2 4 - 2 + 1 =3 5 - 2 + 1 =4 ... 8 - 2 + 1 =7 Now we have our array from 1 to 7 that correspond to the indexed array of 1 to 7. There are other ways to generate that array but this is the most "foolproof". So, now we put this all together to generate yet another array: If TRUE = ROW number, if FALSE = FALSE: B2 = vac = FALSE = FALSE B3 = vac = FALSE = FALSE B4 = vac = TRUE = 3 B5 = vac = FALSE = FALSE B6 = vac = FALSE = FALSE B7 = vac = TRUE = 6 B8 = vac = FALSE = FALSE That array is then passed to the SMALL function: SMALL({F,F,3,F,F,6,F},ROWS($1:1)) As is, that evaluates to the first smallest value which is 3. When drag copied down the ROWS function will increment to $1:2 for the second smallest, $1:3 for the third smallest, etc. Since there is no third smallest that would generate a #NUM! error but remember, we have that taken care of using our "psuedo" error trap. Putting it all together. When copied down this is what you get: INDEX(A$2:A$8,3) = the 3 rd element of the indexed array = 1/4 INDEX(A$2:A$8,6) = the 6 th element of the indexed array = 1/7 INDEX(A$2:A$8,#NUM!) = "" (blank) There you have it! Biff |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Otto M. - Data Matching | Excel Worksheet Functions | |||
| Need to Improve Code Copying/Pasting Between Workbooks | Excel Discussion (Misc queries) | |||
| extract matching text to make report | Excel Discussion (Misc queries) | |||
| Trendline Extract | Charts and Charting in Excel | |||
| Extract multiple records matching criteria from list | Excel Worksheet Functions | |||
Linear Mode
