Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
non duplicate values
Hi Everyone,
I have a following formula in D1 "=IF(COUNTIF($A$1:$B$20,A1)=1,A1,"")" but is there a way when the A1<1 look for next row. my unique list aray is d1-d20. so far i tried following but I am very confused and the code is not working of course:) And I have been posting almost similar questions log time but no one replies. I`m assuming before I was not very clear. If the question is not clear kindly reply me to clarify.... 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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
non duplicate values
Sorry I did not carefully check the forum. Actually there is a similar
question posted previously. It was replied by Mr. Tom which is perfectly working for me. So my question's answer is as follows. Thanks a lot. Dim rng As Range, rw As Long Dim rngA As Range, cell As Range Set rngA = Range(Cells(1, "A"), Cells(Rows.Count, "A").End(xlUp)) Set rng = Range(Cells(1, "B"), Cells(Rows.Count, "B").End(xlUp)) rw = 1 For Each cell In rng If Application.CountIf(rngA, cell.Value) = 0 Then Cells(rw, "E").Value = cell.Value rw = rw + 1 End If Next _________________________________________ wrote: Hi Everyone, I have a following formula in D1 "=IF(COUNTIF($A$1:$B$20,A1)=1,A1,"")" but is there a way when the A1<1 look for next row. my unique list aray is d1-d20. so far i tried following but I am very confused and the code is not working of course:) And I have been posting almost similar questions log time but no one replies. I`m assuming before I was not very clear. If the question is not clear kindly reply me to clarify.... 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Duplicate values | Excel Discussion (Misc queries) | |||
DUPLICATE VALUES | Excel Worksheet Functions | |||
Duplicate values | Excel Worksheet Functions | |||
Duplicate values | Excel Programming | |||
Duplicate values | Excel Programming |