Extract a range from a range

[Guest]

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.

 

[Guest]

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.

---

 

[Biff]

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

 

[Guest]

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

 

[Guest]

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.

 

[Guest]

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.

 

[Guest]

That's ok. Glad you got it working.
Thanks for calling back ..

 

[Guest]

Thank you Biff,its a robust solution.

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

 

[Biff]

You're welcome!

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

Biff