Simplifying Formula (part 1)
 

In A1 = 0
In A2 =IF(B2=***,"",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)),"",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.

Simplifying Formula (part 1)
 

You could try this

select C2:Cn and in the formula bar enter

=IF(ISERROR(SMALL(IF($B$1:$B$20<"",ROW($A1:$A20), ""),ROW($A1:$A20))),"",
INDEX($B$1:$B$20,SMALL(IF($B$1:$B$20<"",ROW($A1:$ A20),""),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)

"PaulW" wrote in message
...
In A1 = 0
In A2 =IF(B2=***,"",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)),"",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.

Simplifying Formula (part 1)
 

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/22 4/22
4/21 4/22
4/22 4/25
4/25 4/25
4/25 4/23
4/21 4/25
4/21 4/26
4/23 4/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())