VBA code to count rows
 

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

VBA code to count rows
 

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



"ub" escreveu:

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

VBA code to count rows
 

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" wrote:

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



"ub" escreveu:

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

VBA code to count rows
 

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:

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" wrote:

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



"ub" escreveu:

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

VBA code to count rows
 

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

VBA code to count rows
 

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

"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