Advance filter needs a header row so run it from a1 as I did and you will
get:
Bldg 1
Bldg1 2
Bldg2 3
Bldg3 4


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"jfcby" wrote in message
...
Don,

Thank you for your help!

How do you specify a header row in the Advance Filter. Because Row One
is the header row. When I run the macro from A2 in E2 Bldg 1 is listed
twice. How can the macro be modified so that the Advance Filter can
begin from A2 and not include Bldg 1 twice?

<BEGIN MACRO CODE

Sub MakeUniqueListAndCount_1()
'Make Unique List & Count

lr = Cells(Rows.Count, "A").End(xlUp).Row
Range("A2:A" & lr).AdvancedFilter Action:=xlFilterCopy, _
CopyToRange:=Range("E2"), Unique:=True
'create formulas
lr = Cells(Rows.Count, "E").End(xlUp).Row
For i = 1 To lr
Cells(i, "F").FormulaR1C1 = "=COUNTIF(C[-5],RC[-1])"
Next i
'move to new sheet
Cells(1, "E").Resize(lr, 2).Cut
Sheets.Add
ActiveSheet.Paste
End Sub

<END MACRO CODE

Thank for your help,
jfcby