software

i have some values in col A in range A2:A12. some values are appearing
more than once. i have put a formula in col B to show values only
once.

=IF(COUNTIF($A$2:$A$12,A2)gt;1,IF(ISNA(VLOOKUP(A2,$B $2:B2,1)),A2,quot;-quot;))

what i meant from this formula is:

if value in A2 is appearing more that once in range A2:A12 then check
in col B in all preceding cells that has it appeared in that range, if
appeared return quot;-quot; otherwise value in cell A.

but this gives error of circular refrence. i changed it many times to
correct it but of no avail.
how can i make this formula correct

thanks in anticipation--
starguy
------------------------------------------------------------------------
starguy's Profile: www.excelforum.com/member.php...oamp;userid=32434
View this thread: www.excelforum.com/showthread...hreadid=533653In B2 put =A2

In B3:

=IF(ISERROR(MATCH(0,COUNTIF(B$2:B2,$A$2:$A$20amp;quot;quot;), 0)),quot;quot;,
INDEX(IF(ISBLANK($A$2:$A$20),quot;quot;,$A$2:$A$20),MATCH( 0,COUNTIF(B$2:B2,$A$2:$A$2
0amp;quot;quot;),0)))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

copy B3 down

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

quot;starguyquot; gt; wrote in
message ...
gt;
gt; i have some values in col A in range A2:A12. some values are appearing
gt; more than once. i have put a formula in col B to show values only
gt; once.
gt;
gt; =IF(COUNTIF($A$2:$A$12,A2)gt;1,IF(ISNA(VLOOKUP(A2,$B $2:B2,1)),A2,quot;-quot;))
gt;
gt; what i meant from this formula is:
gt;
gt; if value in A2 is appearing more that once in range A2:A12 then check
gt; in col B in all preceding cells that has it appeared in that range, if
gt; appeared return quot;-quot; otherwise value in cell A.
gt;
gt; but this gives error of circular refrence. i changed it many times to
gt; correct it but of no avail.
gt; how can i make this formula correct
gt;
gt; thanks in anticipation
gt;
gt;
gt; --
gt; starguy
gt; ------------------------------------------------------------------------
gt; starguy's Profile:
www.excelforum.com/member.php...oamp;userid=32434
gt; View this thread: www.excelforum.com/showthread...hreadid=533653
gt;
Hi,

Try this in B2 and copy down:

=IF(MATCH(A2,$A$2:$A$12,0)lt;ROW(A2)-1,quot;-quot;,A2)

Regands,
Bondi
To fix your original formula to working condition, in B2=IF(COUNTIF($A$2:$A$20,A2)gt;1,IF(ISNA(VLOOKUP(A2,$B $1:B1,1,FALSE)),A2,quot;-quot;),quot;1
onlyquot;)

--

Bob Phillips Wrote:
gt; In B2 put =A2
gt;
gt; In B3:
gt;
gt; =IF(ISERROR(MATCH(0,COUNTIF(B$2:B2,$A$2:$A$20amp;quot;quot;), 0)),quot;quot;,
gt; INDEX(IF(ISBLANK($A$2:$A$20),quot;quot;,$A$2:$A$20),MATCH( 0,COUNTIF(B$2:B2,$A$2:$A$2
gt; 0amp;quot;quot;),0)))
gt;
gt; which is an array formula, it should be committed with
gt; Ctrl-Shift-Enter, not
gt; just Enter.
gt;
gt; copy B3 down
gt;
gt; --
gt; HTH
gt;
gt; Bob Phillips
gt;
gt; (remove nothere from email address if mailing direct)
gt;
gt; quot;starguyquot; gt; wrote
gt; in
gt; message ...
gt; gt;
gt; gt; i have some values in col A in range A2:A12. some values are
gt; appearing
gt; gt; more than once. i have put a formula in col B to show values only
gt; gt; once.
gt; gt;
gt; gt; =IF(COUNTIF($A$2:$A$12,A2)gt;1,IF(ISNA(VLOOKUP(A2,$B $2:B2,1)),A2,quot;-quot;))
gt; gt;
gt; gt; what i meant from this formula is:
gt; gt;
gt; gt; if value in A2 is appearing more that once in range A2:A12 then
gt; check
gt; gt; in col B in all preceding cells that has it appeared in that range,
gt; if
gt; gt; appeared return quot;-quot; otherwise value in cell A.
gt; gt;
gt; gt; but this gives error of circular refrence. i changed it many times
gt; to
gt; gt; correct it but of no avail.
gt; gt; how can i make this formula correct
gt; gt;
gt; gt; thanks in anticipation
gt; gt;
gt; gt;
gt; gt; --
gt; gt; starguy
gt; gt;
gt; ------------------------------------------------------------------------
gt; gt; starguy's Profile:
gt; www.excelforum.com/member.php...oamp;userid=32434
gt; gt; View this thread:
gt; www.excelforum.com/showthread...hreadid=533653
gt; gt;--
Bryan Hessey
------------------------------------------------------------------------
Bryan Hessey's Profile: www.excelforum.com/member.php...oamp;userid=21059
View this thread: www.excelforum.com/showthread...hreadid=533653