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

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" <[email protected]> wrote in
message news:[email protected]...
>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

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

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" <[email protected]> wrote in
> message news:[email protected]...
> >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
>
>
>

"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

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 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
>
>
>

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

> 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
Tools>Formula Auditing>Evaluate 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" <[email protected]> wrote in message
news:[email protected]...
> 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 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
>>
>>
>>

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 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
>
>
>

You're welcome. Thanks for the positive feedback!

Biff

"TUNGANA KURMA RAJU" <[email protected]> wrote in
message news:[email protected]...
> 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 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
>>
>>
>>