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

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

--
Regards
Frank Kabel
Frankfurt, Germany

"Johannes van der Pol" schrieb im Newsbeitrag
...
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

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" schreef in bericht
...
Hi
you may try the following addin:
http://www.rondebruin.nl/easyfilter.htm

--
Regards
Frank Kabel
Frankfurt, Germany

"Johannes van der Pol" schrieb im Newsbeitrag
...
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

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

--
Regards
Frank Kabel
Frankfurt, Germany

"Johannes" schrieb im Newsbeitrag
...
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" schreef in bericht
...
Hi
you may try the following addin:
http://www.rondebruin.nl/easyfilter.htm

--
Regards
Frank Kabel
Frankfurt, Germany

"Johannes van der Pol" schrieb im
Newsbeitrag
...
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

That sounds more like it!

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

Thanks,

Johannes


"Frank Kabel" schreef in bericht
...
Hi
you could create some array formulas but if you have more than 100
records this gets quite slow

--
Regards
Frank Kabel
Frankfurt, Germany

"Johannes" schrieb im Newsbeitrag
...
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" schreef in bericht
...
Hi
you may try the following addin:
http://www.rondebruin.nl/easyfilter.htm

--
Regards
Frank Kabel
Frankfurt, Germany

"Johannes van der Pol" schrieb im
Newsbeitrag
...
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

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('s heet1'!$B$1:$B$1000,
ROW('sheet1'!$B$1:$B$100)),ROW()))),"",INDEX('shee t1'!$A$1:$A$100,SMALL
(IF('sheet1'!$B$1:$B$1000,ROW('sheet1'!$B$1:$B$10 0)),ROW())))
and copy this down

B1:
=IF(ISERROR(INDEX('sheet1'!$B$1:$B$100,SMALL(IF('s heet1'!$B$1:$B$1000,
ROW('sheet1'!$B$1:$B$100)),ROW()))),"",INDEX('shee t1'!$B$1:$B$100,SMALL
(IF('sheet1'!$B$1:$B$1000,ROW('sheet1'!$B$1:$B$10 0)),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(B20,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(RO W()-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(RO W()-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.


--
Regards
Frank Kabel
Frankfurt, Germany

"Johannes" schrieb im Newsbeitrag
...
That sounds more like it!

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

Thanks,

Johannes


"Frank Kabel" schreef in bericht
...
Hi
you could create some array formulas but if you have more than 100
records this gets quite slow

--
Regards
Frank Kabel
Frankfurt, Germany

"Johannes" schrieb im Newsbeitrag
...
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" schreef in bericht
...
Hi
you may try the following addin:
http://www.rondebruin.nl/easyfilter.htm

--
Regards
Frank Kabel
Frankfurt, Germany

"Johannes van der Pol" schrieb im
Newsbeitrag
...
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

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



"Frank Kabel" schreef in bericht
...
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('s heet1'!$B$1:$B$1000,
ROW('sheet1'!$B$1:$B$100)),ROW()))),"",INDEX('shee t1'!$A$1:$A$100,SMALL
(IF('sheet1'!$B$1:$B$1000,ROW('sheet1'!$B$1:$B$10 0)),ROW())))
and copy this down

B1:
=IF(ISERROR(INDEX('sheet1'!$B$1:$B$100,SMALL(IF('s heet1'!$B$1:$B$1000,
ROW('sheet1'!$B$1:$B$100)),ROW()))),"",INDEX('shee t1'!$B$1:$B$100,SMALL
(IF('sheet1'!$B$1:$B$1000,ROW('sheet1'!$B$1:$B$10 0)),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(B20,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(RO W()-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(RO W()-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.


--
Regards
Frank Kabel
Frankfurt, Germany

"Johannes" schrieb im Newsbeitrag
...
That sounds more like it!

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

Thanks,

Johannes


"Frank Kabel" schreef in bericht
...
Hi
you could create some array formulas but if you have more than 100
records this gets quite slow

--
Regards
Frank Kabel
Frankfurt, Germany

"Johannes" schrieb im Newsbeitrag
...
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" schreef in bericht
...
Hi
you may try the following addin:
http://www.rondebruin.nl/easyfilter.htm

--
Regards
Frank Kabel
Frankfurt, Germany

"Johannes van der Pol" schrieb im
Newsbeitrag
...
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