VBA code to count rows

[ub]

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

 

[Marcelo]

have you tried a Pivot table ?
--
pleae click yes if it was helpfull
regards from Brazil
Marcelo



"ub" escreveu:

 

[ub]

Hi Marcelo

I don't know much about Pivot tables. But I can write VBA code, if I get
some hint how the syntax should be
Thanks

 

[Marcelo]

belive me, Pivot table is very simple and easy on this case, if you are
familiary with VB code, the pivot tables will a piece of cake.

have a nice day
--
pleae click yes if it was helpfull
regards from Brazil
Marcelo



"ub" escreveu:

 

[Don Guillett]

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

 

[Ashish Mathur]

Hi,

Assume that your data is in range A16:B18, enter SAM in cell A20 and use the
following array fomula (Ctrl+Shift+Enter)

=SUMPRODUCT(1*(FREQUENCY(IF($A$16:$A$18=$A20,IF($B$16:$B$18<>"",MATCH("~"&$B$16:$B$18,$B$16:$B$18&"",0))),ROW($B$16:$B$18)-ROW($B$15))>0))

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com