Re: extract matching vales
- From: thesaxonuk <thesaxonuk@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Tue, 24 Oct 2006 02:23:02 -0700
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" <thesaxonuk@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:1A1B1129-A407-465D-9A33-0FE498F9F84F@xxxxxxxxxxxxxxxx
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 where:
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
- Follow-Ups:
- Re: extract matching vales
- From: Biff
- Re: extract matching vales
- References:
- Re: extract matching vales
- From: thesaxonuk
- Re: extract matching vales
- From: Biff
- Re: extract matching vales
- Prev by Date: Re: User defined file name on save
- Next by Date: Filter & merged sell border
- Previous by thread: Re: extract matching vales
- Next by thread: Re: extract matching vales
- Index(es):
Relevant Pages
|
