One way. Assumes that col E:F of the source sheet is available

Sub makeuniquelistandcount()
'make list
lr = Cells(Rows.Count, "a").End(xlUp).Row
Range("A1:A" & lr).AdvancedFilter Action:=xlFilterCopy, _
CopyToRange:=Range("E1"), 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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"jfcby" wrote in message
...
Hi Don,

Before Example:
Worksheet 1
Col 1 Col 2 Col 3
Row1 Bldg Rm # Type
Row2 Bldg1 102 6
Row3 Bldg1 105 4
Row4 Bldg2 101 8
Row5 Bldg2 302 6
Row6 Bldg2 501 6
Row7 Bldg3 108 4
Row8 Bldg3 201 6
Row9 Bldg3 309 8
Row10 Bldg3 310 6

After Example:
New Worksheet
Col 1 Col 2
Row1 Bldg Total
Row2 Bldg1 2
Row3 Bldg2 3
Row4 Bldg3 3

Thank you for your help,
jfcby