filter function

[Johannes van der Pol]

Hello all,

I have a long list of items (column A) and say amounts (column B) and want
to create a sublist where only the items are shown with positive amounts.

Can this be done without using (advanced) filter?

My cumbersome solution was to add a counter in column C that increases every
time the amount is positive and then use some "match" and "offset" formula's
to create a list. My feeling says it must be possible to do this easier.

Thanks,

Johannes

 

[Frank Kabel]

Hi
you may try the following addin:
http://www.rondebruin.nl/easyfilter.htm

 

[Johannes]

Thanks, quite usefull add-in though it is not really what i'm looking for.
Am looking for a dynamic kind of link that prevends me from doing this every
time an amount goes from 0 to a positive number. The add-in actually uses a
static copy-pastevalues kind of link.

Still quite helpfull add-in though.

Johannes

 

[Frank Kabel]

Hi
you could create some array formulas but if you have more than 100
records this gets quite slow

 

[Johannes]

That sounds more like it!

You do not know by any chance how that would work?

Thanks,

Johannes

 

[Frank Kabel]

Hi
lets try the following on a second sheet (for positive amounts in
column B):
in A1 on your second sheet enter the following array formula (entered
with CTRL+SHIFT+ENTER):
=IF(ISERROR(INDEX('sheet1'!$A$1:$A$100,SMALL(IF('sheet1'!$B$1:$B$100>0,
ROW('sheet1'!$B$1:$B$100)),ROW()))),"",INDEX('sheet1'!$A$1:$A$100,SMALL
(IF('sheet1'!$B$1:$B$100>0,ROW('sheet1'!$B$1:$B$100)),ROW())))
and copy this down

B1:
=IF(ISERROR(INDEX('sheet1'!$B$1:$B$100,SMALL(IF('sheet1'!$B$1:$B$100>0,
ROW('sheet1'!$B$1:$B$100)),ROW()))),"",INDEX('sheet1'!$B$1:$B$100,SMALL
(IF('sheet1'!$B$1:$B$100>0,ROW('sheet1'!$B$1:$B$100)),ROW())))


Note: These formulas are NOT very efficient and will slow down your
file.

Aladin Akyurek has presented therefore a more efficient formula
solution involving more formulas. Hope I got his approach right for
your sample data If not, Aladin will hopefully reading this and
correct it

---------------------------------
I. On your first worksheet (the source) do the following:
1. Insert 1 row before the source data such that A2:B100 houses the
data.

2. In E1 enter: 0

3. In E2 enter & copy down to E100:

=IF(B2>0,LOOKUP(9.99999999999999E+307,$E$1:E1)+1,"")


II. on your second worksshet (destination) enter the following:
1. In A1 enter:
=LOOKUP(9.9999999999999E+307,Sheet1!E2:E100)

2. In A2 enter & copy down:
=IF(ROW()-ROW(A$2)+1<=$A$1,INDEX(Sheet1!$A$2:$A$100,MATCH(ROW()-ROW(A$2
)+1,Sheet1!$E$2:$E$100)),"")

3. In B2 enter
=IF(ROW()-ROW(A$2)+1<=$A$1,INDEX(Sheet1!$B$2:$B$100,MATCH(ROW()-ROW(A$2
)+1,Sheet1!$E$2:$E$100)),"")

-------------------------------------

So now it's your choice. Personally I like Aladin's approach as it is
faster (and robust...). If you have only a few records my array
formulas are easier to setup.

 

[Johannes]

EXCELLENT!!! Took me some time to rephrase it to Dutch Excel and (of course)
different areas, but both work perfectly. Especially like the simplicity of
the second one, though the first one has some advantages as well..

Perfect.

Thanks again,

Johannes