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

[jzgers]

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

 

[jzgers]

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

 

[Domenic]

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$21,MATCH(0,IF(E4:E$21>0,COUNTIF(G$3:G3,D4$21)),0))

Hope this helps!

 

[jzgers]

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

 

[Peo Sjoblom]

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

 

[jzgers]

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

 

[Domenic]

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?