Simplifying Formula (part 1)

[Guest]

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)),"",VLOOKUP(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.

 

[Bob Phillips]

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)

 

[Herbert Seidenberg]

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())