Advanced Filter and Unique Count Code

[adevine]

Hello,

I have written some code that filters two columns - however when filtered i have multiple entries of the same line - ie data about employees - is there a way i can use the empoyee ID as a unique identifier and add some sort of count function. I have used the function that copies the unique numbers into another place on the worksheet however as soon as this is applied it removes the advanced sort and therfore all the numbers are in the wrong place - as a fomula i have used =SUM(IF(FREQUENCY('Application Management'!A8:A1000,'Application Management'!A8:A1000)>0,1))

any help would be great

Ant

[Simon Lloyd]

Welcome to VBAX!, firstly, why not take the time to tell us what you want to achieve, then, supply us the code you have written because besides helping you with your query we can often imporve what you have already written making it easier for you to read/understand, and lastly, when you have enough posts upload a workbook so we can help you directly with that. :)

[adevine]

The following is the code ive written, i have specific job levels that realte to rates - i need to count the amount of people at that particular billing rate - however on the master data record that i have- sue to constuduction restraints they each have several entry lines - which means if i do an odinary count i get nearly terble the amount of employees.






Public Function App_Management_Practice()
'
' Aplication management Macro
Dim compiler, Utilisation, sheet As String
compiler = "RMT_FY09_Data_Compiler.xlsx"
Utilisation = "book1.xlsm"
sheet = "Application Management"
Report = "Application_Management_Report"
'INTERN = 24
'ENT = 24
'EXP = 45
'INTERM = 44
'MAS = 60
'MG1 = 55
'SPE = 48

'
Workbooks.Open Filename:="C:\Users\devinean\Desktop\" & compiler
Workbooks(compiler).Activate
Worksheets("DATA RAW").Select
ActiveSheet.Range("$A$4:$BU$1000").AutoFilter Field:=8
ActiveSheet.Range("$A$4:$BU$1000").AutoFilter Field:=8, Criteria1:=sheet
Columns("A:DQ").Hidden = False
Range("A1:DQ2000").Copy
Workbooks(Utilisation).Activate
Worksheets(sheet).Select
Range("A6").PasteSpecial Paste:=xlPasteValues
ActiveSheet.AutoFilterMode = False
ActiveSheet.Range("$A$9:$Y$1000").AutoFilter Field:=20, Criteria1:="24"
ActiveSheet.Range("$A$9:$Y$1000").AutoFilter Field:=6, Criteria1:="ENT"
Worksheets(Report).Select
Range("C7").Value = "=SUM(IF(FREQUENCY('Application Management'!A8:A1000,'Application Management'!A8:A1000)>0,1))"



End Function