duplicated values
 

Hi Everyone,
I have a following formula in D1
"=IF(COUNTIF($A$1:$B$20,A1)=1,A1,"")"
but the problem I just want enter in a cell in a sequence, if the value
is udplicated then look for the next cell. I tried folling but I think
I`m completely wrong, because the code is not working:)
Dim x, n
Dim ncell
For n = 1 To 19
ncell = Sheet2.Range("b" & n)
x = Application.WorksheetFunction.CountIf(Sheet1.Range ("a1:b10"),
ncell)
If x = 1 Then
Sheet2.Range("d" & n) = Sheet2.Range("b" & n)
n = n + 1
End If
Next
can anyone help me?

duplicated values
 

Try this:

=IF(ISERR(SMALL(IF(COUNTIF($A$1:$B$20,$A$1:$A$20)= 1,ROW(INDIRECT("1:"&ROWS($A$1:$A$20)))),ROWS($1:1) )),"",INDEX($A$1:$A$20,SMALL(IF(COUNTIF($A$1:$B$20 ,$A$1:$A$20)=1,ROW(INDIRECT("1:"&ROWS($A$1:$A$20)) )),ROWS($1:1))))

ctrl+shift+enter (not just enter)
copy down as far as needed



"baha" wrote:

Hi Everyone,
I have a following formula in D1
"=IF(COUNTIF($A$1:$B$20,A1)=1,A1,"")"
but the problem I just want enter in a cell in a sequence, if the value
is udplicated then look for the next cell. I tried folling but I think
I`m completely wrong, because the code is not working:)
Dim x, n
Dim ncell
For n = 1 To 19
ncell = Sheet2.Range("b" & n)
x = Application.WorksheetFunction.CountIf(Sheet1.Range ("a1:b10"),
ncell)
If x = 1 Then
Sheet2.Range("d" & n) = Sheet2.Range("b" & n)
n = n + 1
End If
Next
can anyone help me?

duplicated values
 

thanks a lot for your help. i works fine fine, i just addjust those $
arrays,then ok .
thank you again
baha
Teethless mama wrote:
Try this:

=IF(ISERR(SMALL(IF(COUNTIF($A$1:$B$20,$A$1:$A$20)= 1,ROW(INDIRECT("1:"&ROWS($A$1:$A$20)))),ROWS($1:1) )),"",INDEX($A$1:$A$20,SMALL(IF(COUNTIF($A$1:$B$20 ,$A$1:$A$20)=1,ROW(INDIRECT("1:"&ROWS($A$1:$A$20)) )),ROWS($1:1))))

ctrl+shift+enter (not just enter)
copy down as far as needed



"baha" wrote:

Hi Everyone,
I have a following formula in D1
"=IF(COUNTIF($A$1:$B$20,A1)=1,A1,"")"
but the problem I just want enter in a cell in a sequence, if the value
is udplicated then look for the next cell. I tried folling but I think
I`m completely wrong, because the code is not working:)
Dim x, n
Dim ncell
For n = 1 To 19
ncell = Sheet2.Range("b" & n)
x = Application.WorksheetFunction.CountIf(Sheet1.Range ("a1:b10"),
ncell)
If x = 1 Then
Sheet2.Range("d" & n) = Sheet2.Range("b" & n)
n = n + 1
End If
Next
can anyone help me?