I am having a range a2:a100 student'sNames,b2:b100-standard(std1,std2
etc...),c2:c100-Marks.
Basing on D1 value(say D1=std2) ,I want to extract std2 values of range
a2:c100 to D2:F100( all 3 columns data).Blank range shoud shown as
Blank("").I don't want to it using filter.So,please don't advise me using
filter options on the list.Many thanks if anybody gives me a robust formula.

"TUNGANA KURMA RAJU" wrote:
I am having a range a2:a100 student's Names,
b2:b100-standard(std1,std2 etc...), c2:c100-Marks.
Basing on D1 value(say D1=std2), I want to extract std2 values of range
a2:c100 to D2:F100( all 3 columns data).
Blank range should shown as Blank("").
I don't want to it using filter.

One way using non-array formulas (& simple entire col references) ..

Create a DV in D1 to select the standard, viz:
Data Validation, Allow: List, Source: Std1,Std2,Std3,etc
Paste the col headers in A1:C1 into E1:G1

Then place in D2:
=IF(B2="","",IF(B2=$D$1,ROW(),""))

Place in E2:
=IF(ROW(A1)COUNT($D:$D),"",INDEX(A:A,MATCH(SMALL( $D:$D,ROW(A1)),$D:$D,0)))
Copy E2 to G2

Just select D2:G2 and copy down as far as required to cover the max expected
extent of source data. The required results depending on the DV selection
made in D1 will display in cols E to G, all neatly bunched at the top. Mask
col D by formatting the font in white - except for the DV cell in D1.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Hi!

Try this:

Entered as an array in D2:

=IF(ROWS($1:1)<=COUNTIF($B$2:$B$100,$D$1),INDEX(A$ 2:A$100,SMALL(IF($B$2:$B$100=$D$1,ROW(A$2:A$100)-ROW(A$2)+1),ROWS($1:1))),"")

Copy across to F2 then down.

Biff

"TUNGANA KURMA RAJU" wrote in
message ...
I am having a range a2:a100 student'sNames,b2:b100-standard(std1,std2
etc...),c2:c100-Marks.
Basing on D1 value(say D1=std2) ,I want to extract std2 values of range
a2:c100 to D2:F100( all 3 columns data).Blank range shoud shown as
Blank("").I don't want to it using filter.So,please don't advise me using
filter options on the list.Many thanks if anybody gives me a robust
formula.

Sorry Everybody,I put my question wrong.Subject be corrected as "Exitract a
list from a range"
Message:2 nd paragraph=Basing on D1 value(say D1=std2) ,I want to extract
all the std2 values as a list range viz in d2:f.... range as array range.No
filter option

"Max" wrote:

"TUNGANA KURMA RAJU" wrote:
I am having a range a2:a100 student's Names,
b2:b100-standard(std1,std2 etc...), c2:c100-Marks.
Basing on D1 value(say D1=std2), I want to extract std2 values of range
a2:c100 to D2:F100( all 3 columns data).
Blank range should shown as Blank("").
I don't want to it using filter.

One way using non-array formulas (& simple entire col references) ..

Create a DV in D1 to select the standard, viz:
Data Validation, Allow: List, Source: Std1,Std2,Std3,etc
Paste the col headers in A1:C1 into E1:G1

Then place in D2:
=IF(B2="","",IF(B2=$D$1,ROW(),""))

Place in E2:
=IF(ROW(A1)COUNT($D:$D),"",INDEX(A:A,MATCH(SMALL( $D:$D,ROW(A1)),$D:$D,0)))
Copy E2 to G2

Just select D2:G2 and copy down as far as required to cover the max expected
extent of source data. The required results depending on the DV selection
made in D1 will display in cols E to G, all neatly bunched at the top. Mask
col D by formatting the font in white - except for the DV cell in D1.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

I did read all of whatever's posted, Tungana <g! No prob if you don't want
the DV in D1, which will simply house the input for the standard, eg: Std2.
The DV is simply to facilitate selection, and avoid risk of input typo
errors. The non-array set-up using the simpler entire col references will
still return the results you seek. It's just one way to get there. Your
preference, of course.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"TUNGANA KURMA RAJU" wrote:
Sorry Everybody,I put my question wrong.Subject be corrected as "Exitract a
list from a range"
Message:2 nd paragraph=Basing on D1 value(say D1=std2) ,I want to extract
all the std2 values as a list range viz in d2:f.... range as array range.No
filter option

Thank you max,I am sorry I didn't checked your answer properly.This time I
did it properly Its working fine.Thank you very much for your help.

"Max" wrote:

"TUNGANA KURMA RAJU" wrote:
I am having a range a2:a100 student's Names,
b2:b100-standard(std1,std2 etc...), c2:c100-Marks.
Basing on D1 value(say D1=std2), I want to extract std2 values of range
a2:c100 to D2:F100( all 3 columns data).
Blank range should shown as Blank("").
I don't want to it using filter.

One way using non-array formulas (& simple entire col references) ..

Create a DV in D1 to select the standard, viz:
Data Validation, Allow: List, Source: Std1,Std2,Std3,etc
Paste the col headers in A1:C1 into E1:G1

Then place in D2:
=IF(B2="","",IF(B2=$D$1,ROW(),""))

Place in E2:
=IF(ROW(A1)COUNT($D:$D),"",INDEX(A:A,MATCH(SMALL( $D:$D,ROW(A1)),$D:$D,0)))
Copy E2 to G2

Just select D2:G2 and copy down as far as required to cover the max expected
extent of source data. The required results depending on the DV selection
made in D1 will display in cols E to G, all neatly bunched at the top. Mask
col D by formatting the font in white - except for the DV cell in D1.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

That's ok. Glad you got it working.
Thanks for calling back ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"TUNGANA KURMA RAJU" wrote:
Thank you max,I am sorry I didn't checked your answer properly.This time I
did it properly Its working fine.Thank you very much for your help.

Thank you Biff,its a robust solution.

"Biff" wrote:

Hi!

Try this:

Entered as an array in D2:

=IF(ROWS($1:1)<=COUNTIF($B$2:$B$100,$D$1),INDEX(A$ 2:A$100,SMALL(IF($B$2:$B$100=$D$1,ROW(A$2:A$100)-ROW(A$2)+1),ROWS($1:1))),"")

Copy across to F2 then down.

Biff

"TUNGANA KURMA RAJU" wrote in
message ...
I am having a range a2:a100 student'sNames,b2:b100-standard(std1,std2
etc...),c2:c100-Marks.
Basing on D1 value(say D1=std2) ,I want to extract std2 values of range
a2:c100 to D2:F100( all 3 columns data).Blank range shoud shown as
Blank("").I don't want to it using filter.So,please don't advise me using
filter options on the list.Many thanks if anybody gives me a robust
formula.

You're welcome!

Did you see my last reply to the top/bottom 3 list post?

Biff

"TUNGANA KURMA RAJU" wrote in
message ...
Thank you Biff,its a robust solution.

"Biff" wrote:

Hi!

Try this:

Entered as an array in D2:

=IF(ROWS($1:1)<=COUNTIF($B$2:$B$100,$D$1),INDEX(A$ 2:A$100,SMALL(IF($B$2:$B$100=$D$1,ROW(A$2:A$100)-ROW(A$2)+1),ROWS($1:1))),"")

Copy across to F2 then down.

Biff

"TUNGANA KURMA RAJU" wrote
in
message ...
I am having a range a2:a100 student'sNames,b2:b100-standard(std1,std2
etc...),c2:c100-Marks.
Basing on D1 value(say D1=std2) ,I want to extract std2 values of
range
a2:c100 to D2:F100( all 3 columns data).Blank range shoud shown as
Blank("").I don't want to it using filter.So,please don't advise me
using
filter options on the list.Many thanks if anybody gives me a robust
formula.