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

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

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

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

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$210,COUNTIF(G$3:G3 ,D4:D$21)),0))

Hope this helps!

In article .com,
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

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

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

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

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



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

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

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

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

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 . com,
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