existing unique value formula help (index(match(countif)))

so - someone on this groups list posted the following awesome and
simple formula to pull unique values out of one column (thank you very
much, by the way)

=INDEX($D$4:$D$21,MATCH(0,COUNTIF($G$3:G3,$D$4:$D$21),0))

this formula (when you shift-control enter it and paste down column g)
will list all the unique values from column d. this is a great formula.

however, can anybody suggest a way i could adapt this formula to the
following 2 problems?

1. where instead of pasting down a column, i need a unique value every
3 cells down a column?

2. where i only want unique values that correspond to positive values
in a neighboring column?

thanks!!
jason gers
glaceau
whitestone, queens, nyc

ok ok i figured out the answer to question 1... this one was actually
easy -- as long as the range on the countif statement includes all the
other values, it won't start the list over again... but any clues on
the second one?

jason gers
glaceau

> 2. where i only want unique values that correspond to positive values
> in a neighboring column?

Assuming that the 'neighbouring column' is Column E, try...

=INDEX(D4:D$21,MATCH(0,IF(E4:E$21>0,COUNTIF(G$3:G3,D4:D$21)),0))

Hope this helps!

In article <[email protected]>,
[email protected] wrote:

> so - someone on this groups list posted the following awesome and
> simple formula to pull unique values out of one column (thank you very
> much, by the way)
>
> =INDEX($D$4:$D$21,MATCH(0,COUNTIF($G$3:G3,$D$4:$D$21),0))
>
> this formula (when you shift-control enter it and paste down column g)
> will list all the unique values from column d. this is a great formula.
>
> however, can anybody suggest a way i could adapt this formula to the
> following 2 problems?
>
> 1. where instead of pasting down a column, i need a unique value every
> 3 cells down a column?
>
> 2. where i only want unique values that correspond to positive values
> in a neighboring column?
>
> thanks!!
> jason gers
> glaceau
> whitestone, queens, nyc

hmm... not working for me, just gives me an error message on the "0" in
the if statement.

You must have copied some extra characters or done somthing else
incorrectly, it shouldn't stop on that part. The formula works fine


--

Regards,

Peo Sjoblom

http://nwexcelsolutions.com



<[email protected]> wrote in message
news:[email protected]...
> hmm... not working for me, just gives me an error message on the "0" in
> the if statement.
>

okay i'm not getting an error message anymore, but it's not bringing up
the right information. the formula is bringing up values from "d" with
zeros in "e"; it misses values from d with positive values in "e" and
finally, when you delete one from the top of the list, the rest of the
cloumn should adjust, but it isn't... any clues? if not it's okay, it's
not that much manual work, the original formula is a big help for me
though. thanks again.
jason

It's likely that the numbers in Column E are being recognized as text.
Try the following...

1) Select an empty cell

2) Edit > Copy

3) Select Column E

4) Edit > Paste Special > Add > Ok

Does this help?

In article <[email protected]>,
[email protected] wrote:

> okay i'm not getting an error message anymore, but it's not bringing up
> the right information. the formula is bringing up values from "d" with
> zeros in "e"; it misses values from d with positive values in "e" and
> finally, when you delete one from the top of the list, the rest of the
> cloumn should adjust, but it isn't... any clues? if not it's okay, it's
> not that much manual work, the original formula is a big help for me
> though. thanks again.
> jason