software

In A1 = 0
In A2 =IF(B2=***,quot;quot;,AVERAGE($A$1:A1)*2 1)

Dragging A2 down as far as needed.

In C1 = 1
In C2 = C1 1

Dragging down C2 as far as needed.

In D1
=IF(ISERROR(VLOOKUP(C1,$A$2:$B$40,2,FALSE)),quot;quot;,VLO OKUP(C1,$A$2:$B$40,2,FALSE))

What this does essentially is take a lot of data and remove the gaps. I use
this *alot* for things like productivity for example, where you only want a
list of what has been done. Or maybe where theres a list of dates, and only
applicable dates what to be used (say a Vlookup in column C that see how many
hours are worked, so the dates with 0 in C don't count)

Is there an easy way to simplify this? The way im doing it is simple and
effective, but the sheets im ending up with are starting to get too big, 1 I
did recently was 17meg to end up with a little table....
Even if you can't make the entire thing smaller, my main worry is the
AVERAGE(A2:A7)*2 1 which seems quite clumsy.

You could try this

select C2:Cn and in the formula bar enter

=IF(ISERROR(SMALL(IF($B$1:$B$20lt;gt;quot;quot;,ROW($A1:$A20), quot;quot;),ROW($A1:$A20))),quot;quot;,
INDEX($B$1:$B$20,SMALL(IF($B$1:$B$20lt;gt;quot;quot;,ROW($A1:$ A20),quot;quot;),ROW($A1:$A20))))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

adjust the ranges to suit

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

quot;PaulWquot; gt; wrote in message
...
gt; In A1 = 0
gt; In A2 =IF(B2=***,quot;quot;,AVERAGE($A$1:A1)*2 1)
gt;
gt; Dragging A2 down as far as needed.
gt;
gt; In C1 = 1
gt; In C2 = C1 1
gt;
gt; Dragging down C2 as far as needed.
gt;
gt; In D1
gt;
=IF(ISERROR(VLOOKUP(C1,$A$2:$B$40,2,FALSE)),quot;quot;,VLO OKUP(C1,$A$2:$B$40,2,FALSE
))
gt;
gt; What this does essentially is take a lot of data and remove the gaps. I
use
gt; this *alot* for things like productivity for example, where you only want
a
gt; list of what has been done. Or maybe where theres a list of dates, and
only
gt; applicable dates what to be used (say a Vlookup in column C that see how
many
gt; hours are worked, so the dates with 0 in C don't count)
gt;
gt; Is there an easy way to simplify this? The way im doing it is simple and
gt; effective, but the sheets im ending up with are starting to get too big, 1
I
gt; did recently was 17meg to end up with a little table....
gt; Even if you can't make the entire thing smaller, my main worry is the
gt; AVERAGE(A2:A7)*2 1 which seems quite clumsy.
You could use Advanced Filter
which needs only one formula in one cell.
Assume you want to filter today's date out of your list:

ListA
4/224/22
4/214/22
4/224/25
4/254/25
4/254/23
4/214/25
4/214/26
4/234/25
4/25
4/26
4/21
4/25

Criteria
Test
TRUE

Name ListA (12 cells) and Criteria (2 cells)
Where it says TRUE enter this formula:
=NOT(ListA=TODAY())