Choosing Data and skipping other

[Guest]

I wish to have Excel choos items from another worksheet according to the
criteria I set (ie: if =1, then choose ... but skip if not = 1 and select the
next item = to 1 ...) I can set this up so it replicates the other worksheet
- but I end up with a lot of blank lines - I don't want the lines blank - but
only have lines with information = to my querry.

Thanks,
David

 

[Max]

One way using non-array formulas ..

Demo file available at: http://www.savefile.com/files/4090857
Choosing_Data_and_skipping_other_DavidBr318_wksht.xls

Assume you have in Sheet1, cols A & B, data from row2 down, where the key
col is col B

Staff Key col
Name1 2
Name2 1
Name3 2
Name4 1
Name5 1

Use an empty col to the right, say col E ?
Put in E2: =IF(B2="","",IF(B2=1,ROW(),""))
Copy E2 down to say, E10, to cover the max expected data range
(Leave E1 empty)

(Adapt the criteria to suit in col E ..)

In Sheet2
--------
Paste the same headers into A1:B1 : Staff, Key col

Put in A2:
=IF(ISERROR(SMALL(Sheet1!$E:$E,ROWS($A$1:A1))),"",
INDEX(Sheet1!A:A,MATCH(
SMALL(Sheet1!$E:$E,ROWS($A$1:A1)),Sheet1!$E:$E,0)))

Copy A2 across to B2, fill down to B10
(cover the same range size as done in col E in Sheet1)

Sheet2 will return only the lines satisfying the criteria (i.e. those = 1 in
the key col in Sheet1), all neatly bunched at the top, viz.:

Staff Key col
Name2 1
Name4 1
Name5 1
(blank rows below)

Adapt to suit ..

 

[Richard Buttrey]

I wish to have Excel choos items from another worksheet according to the
criteria I set (ie: if =1, then choose ... but skip if not = 1 and select the
next item = to 1 ...) I can set this up so it replicates the other worksheet
- but I end up with a lot of blank lines - I don't want the lines blank - but
only have lines with information = to my querry.

Thanks,
David

Have you tried Data Filter Advanced?

Rgds
__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________