Given your sample this should make your list starting at column I. Just
assign to a button or shape.
In this case make VERY sure that col H is EMPTY or ALL will be erased.

Sub makeuniquelist_placeformulas()
Application.ScreenUpdating = False
Range("j1").CurrentRegion.ClearContents
alr = Cells(Rows.Count, "a").End(xlUp).Row
Range("A1:a" & alr).AdvancedFilter Action:=xlFilterCopy, _
CopyToRange:=Range("I1"), Unique:=True
Range("b1:b" & alr).AdvancedFilter Action:=xlFilterCopy, _
CopyToRange:=Range("j1"), Unique:=True
lr = Cells(Rows.Count, "j").End(xlUp).Row
Range("j2:J" & lr).Copy
Range("j1").PasteSpecial Paste:=xlPasteAll, Transpose:=True
ilr = Cells(Rows.Count, "i").End(xlUp).Row
Cells(lr, "J").Resize(lr - ilr).ClearContents
'End Sub
'Sub placeformulas()
lr = Cells(Rows.Count, "i").End(xlUp).Row
Range("j2:l" & lr).Formula = _
"=IF(ISNA(SUMPRODUCT(($A$2:$A$" & alr & " =$I2)* " & _
"($B$2:$B$" & alr & "=J$1))) " & ","""",SUMPRODUCT((" & _
"$A$2:$A$" & alr & "=$I2)*($B$2:$B$" & alr & "=J$1)))"
Application.ScreenUpdating = True
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"ub" wrote in message
...
Hi
My sheets has 9000 rows and the data is structured as

Column A Coulmn B _____Column C to H
SAM USER1
SAM USER1
SAM USER1
SAM USER2
SAM USER3
PAM USER2
PAM USER2
PAM USER3
PAM USER3
I want to look at Column A, and for each name in coulmn A, I want to count
how many times different users called in for service. The result in the
above
example should be
SAM User1 User2 User3
5 3 1 1
The challange is the names in Column A and Coulmn B are always variable-
no
fixed number of users and no fix number of the representatives
Can someone please advise syntax of the code