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)1,IF(ISNA(VLOOKUP(A2,$B $2:B2,1)),A2,"-"))

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 "-" 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: http://www.excelforum.com/member.php...o&userid=32434
View this thread: http://www.excelforum.com/showthread...hreadid=533653

In B2 put =A2

In B3:

=IF(ISERROR(MATCH(0,COUNTIF(B$2:B2,$A$2:$A$20&""), 0)),"",
INDEX(IF(ISBLANK($A$2:$A$20),"",$A$2:$A$20),MATCH( 0,COUNTIF(B$2:B2,$A$2:$A$2
0&""),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)

"starguy" wrote in
message ...

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)1,IF(ISNA(VLOOKUP(A2,$B $2:B2,1)),A2,"-"))

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 "-" 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:
http://www.excelforum.com/member.php...o&userid=32434
View this thread: http://www.excelforum.com/showthread...hreadid=533653

Hi,

Try this in B2 and copy down:

=IF(MATCH(A2,$A$2:$A$12,0)<ROW(A2)-1,"-",A2)

Regands,
Bondi

To fix your original formula to working condition, in B2


=IF(COUNTIF($A$2:$A$20,A2)1,IF(ISNA(VLOOKUP(A2,$B $1:B1,1,FALSE)),A2,"-"),"1
only")

--

Bob Phillips Wrote:
In B2 put =A2

In B3:

=IF(ISERROR(MATCH(0,COUNTIF(B$2:B2,$A$2:$A$20&""), 0)),"",
INDEX(IF(ISBLANK($A$2:$A$20),"",$A$2:$A$20),MATCH( 0,COUNTIF(B$2:B2,$A$2:$A$2
0&""),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)

"starguy" wrote
in
message ...

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)1,IF(ISNA(VLOOKUP(A2,$B $2:B2,1)),A2,"-"))

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 "-" 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:
http://www.excelforum.com/member.php...o&userid=32434
View this thread:
http://www.excelforum.com/showthread...hreadid=533653



--
Bryan Hessey
------------------------------------------------------------------------
Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059
View this thread: http://www.excelforum.com/showthread...hreadid=533653