Count records in column and output to a table

[RFLundgren]

Hi all

I have a sheet with one of the columns containing customer names. I need to loop through the column and find a count of records by customer, to them output to a table.

Customer
Customer 3
Customer 1
Customer 2
Customer 3
Customer 2
Customer 1
Customer 2
Customer 2
Customer 2
Customer 2
Customer 2
Customer 2
Customer 2
Customer 2
Customer 2
Customer 2
Customer 3
Customer 1
Customer 1
Customer 1
Customer 3
Customer 3
Customer 3
Customer 3
Customer 1

I need to output it to a table as below.

Customer	Count of tasks
Customer 1	6
Customer 2	12
Customer 3	3

Number of records changes on a regular basis.

Any help most appreciated. Many thanks

 

[Peter_SSs]

See if you could adapt something like this.

Sub CustCount()
  Dim d As Object
  Dim a As Variant
  Dim i As Long
  
  Set d = CreateObject("Scripting.Dictionary")
  a = Range("A2", Range("A" & Rows.Count).End(xlUp)).Value
  For i = 1 To UBound(a)
    d(a(i, 1)) = d(a(i, 1)) + 1
  Next i
  Range("C2:D2").Resize(d.Count).Value = Application.Transpose(Array(d.keys, d.Items))
End Sub

My sample data and results:

RFLundgren.xlsm
	A	B	C	D
1	Customer
2	Customer 3		Customer 3	7
3	Customer 1		Customer 1	6
4	Customer 2		Customer 2	12
5	Customer 3
6	Customer 2
7	Customer 1
8	Customer 2
9	Customer 2
10	Customer 2
11	Customer 2
12	Customer 2
13	Customer 2
14	Customer 2
15	Customer 2
16	Customer 2
17	Customer 2
18	Customer 3
19	Customer 1
20	Customer 1
21	Customer 1
22	Customer 3
23	Customer 3
24	Customer 3
25	Customer 3
26	Customer 1
Sheet1

 

[Peter_SSs]

You could also do this very easily with formulas.

RFLundgren.xlsm
	A	B	C	D
1	Customer
2	Customer 3		Customer 3	7
3	Customer 1		Customer 1	6
4	Customer 2		Customer 2	12
5	Customer 3
6	Customer 2
7	Customer 1
8	Customer 2
9	Customer 2
10	Customer 2
11	Customer 2
12	Customer 2
13	Customer 2
14	Customer 2
15	Customer 2
16	Customer 2
17	Customer 2
18	Customer 3
19	Customer 1
20	Customer 1
21	Customer 1
22	Customer 3
23	Customer 3
24	Customer 3
25	Customer 3
26	Customer 1
Sheet1

Cell Formulas
Range	Formula
C2:C4	C2	=UNIQUE(A2:A26)
D2:D4	D2	=COUNTIF(A2:A26,C2#)
Dynamic array formulas.

 

[RFLundgren]

Hi Peter

Many thanks, words perfectly! It does need to be done in VBA as the number of rows in the sheet can vary greatly from month to month and the result will actually be placed into another workbook.

I may also need to do a variation of this, where it also does a count but based on criteria in a different column. I will see how I go, and pop back if I run into any trouble.

Thanks!

 

[alansidman]

An alternative is to use Power Query

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Customer", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Customer"}, {{"Count", each Table.RowCount(_), Int64.Type}})
in
    #"Grouped Rows"

Customer	Count
Customer 3	7
Customer 1	6
Customer 2	12

 

[RFLundgren]

Hey Alan

Thanks! Always nice to have alternate options. Also gives me something else to have a look into as have never used Power Query, so something new to learn potentially

 

[Peter_SSs]

Hi Peter

Many thanks, words perfectly!

You're welcome. Thanks for the follow-up.