Solved: Macro for countif

[sebas1102]

i have a list of items in a column. some items may be the same and gets repeated in the came column.
I want to extract every item name in another spreadsheet column only once so that those duplicates will not be repeated again.

Is it possible?

[Bob Phillips]

i have a list of items in a column. some items may be the same and gets repeated in the came column.
I want to extract every item name in another spreadsheet column only once so that those duplicates will not be repeated again.

Is it possible?

Look at Data>Filter>ADvanced Filter, it has an option for Unique Entries.

[sebas1102]

yup but i need it in macro to add to another sheet so that there will be no duplicates... i need to collate data. i need to do a countif in a new sheet and if counif>0 in the 2nd sheet means the item has already been added so it can proceed on to the next row.
Can u help me? i am very lost...

[TonyJollans]

You can still use Advaned filter - trick is to start from the target sheet - record yourself setting it up to get code - it's pretty straightforward.

[sebas1102]

=( still cannot be done bcos it is different workbook....*pluck hair*

[johnske]

yup but i need it in macro to add to another sheet so that there will be no duplicates... i need to collate data. i need to do a countif in a new sheet and if counif>0 in the 2nd sheet means the item has already been added so it can proceed on to the next row.
Can u help me? i am very lost...

Not sure of the details of what you want to do here, but you can use collections to extract unique items e.g.
[vba]Option Explicit
'
Sub GetUniqueItems()
'
Dim UniqueItem As Collection, Cell As Range, N As Long
'
Set UniqueItem = New Collection
'
Application.ScreenUpdating = False
'
On Error Resume Next
'get all the unique items in column A
For Each Cell In Range("A1", Range("A" & Rows.Count).End(xlUp))
'can't have duplicates in a collection
UniqueItem.Add CStr(Cell), CStr(Cell)
Next
'
'put the unique items in column B
For N = 1 To UniqueItem.Count
Range("B" & N) = UniqueItem(N)
Next
Application.ScreenUpdating = True
'
End Sub[/vba]

[TonyJollans]

=( still cannot be done bcos it is different workbook....*pluck hair*

You seem to be looking for excuses. You didn't say it was a different workbook but, anyway, it *can* still be done. Have you tried it?