PDA

View Full Version : Solved: Macro for countif



sebas1102
05-18-2006, 08:15 AM
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
05-18-2006, 09:08 AM
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
05-18-2006, 05:29 PM
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
05-18-2006, 05:42 PM
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
05-19-2006, 12:27 AM
=( still cannot be done bcos it is different workbook....*pluck hair*

johnske
05-19-2006, 01:10 AM
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.
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

TonyJollans
05-19-2006, 03:34 AM
=( 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?