Interesting.

There are a variety of ways to eliminate the blanks in column C.

Which of these ways would best suit as a defined range to be used in a
dropdown list for example



"Gary''s Student" wrote:

In C1 enter:

=IF(OR(B1="",COUNTIF($A$1:A1,A1)1),"",A1) and copy down:

apples 1 apples
grapes
pears 3 pears
oranges
apples 5

There are a variety of ways to eliminate the blanks in column C.
--
Gary''s Student - gsnu200909


"gootroots" wrote:

This issue has alluded me for a while. Is there a formula that could be used
in "C" that will bring back unique values contained in "A" but only bring the
value back if that value has a row value showing in "B"

A1 = apples B1 = 1 C1 = apples
A2 = grapes B2 = C2 = pears
A3 = pears B3 = 3 C3 =
A4 = oranges B4 = C4 =
A5 = apples B5 = 5 C5 =

A hard one for sure, although I would like to know if there is such a formula.

Thank you for your time.