For each unique value in a column, filter and sum the number of visible rows then copy this to another sheet

[ishanalikhan]

Learning Excel VBA on the go to automate a few processes at work and web search didn't provide an answer for this. I have a worksheet with about 15 columns that acts as a log for the work performed daily (Dates in column 1). These tasks are categorized for data analysis purposes (Categories in Column 11). The output is stored on Metrics sheet. I'm copying the dates column to a temp sheet and removing duplicates (using RemoveDuplicates) and then pasting the output data as column headers in Metrics sheet. I want to use these values to filter the original data and get the required numbers, but have been unable to do so. I'm using:

.autofilter field=1, Criteria:= Sheets("Metric").Range("B1").Value

I tried by specifying the date format as well, but in vain.

I would like to use a for loop for each unique value in the date column.

Any help is greatly appreciated. Thank you.

 

[DanteAmor]

Hi and welcome to MrExcel.

I'm not sure what data you want to copy after doing the filter, and where exactly you want to paste it.

Here are a couple of examples to filter with unique values.

VBA code to copy template worksheet according to list, list has duplicates

Hi All new member here! I have a situation where i need to copy a template tab multiple times, and name these new tabs as per a pre-defined list. I found the below code on this forum Sub makeSheets() Dim sh1 As Worksheet, sh2 As Worksheet, c As Range Set sh1 = Sheets("Template") Set sh2 =...

www.mrexcel.com

Copy data to multiple sheets when store # changes & Name sheet

Summary: I have a spreadsheet with multiple store performance metrics on it and need to break them out to separate tabs. The table has data in columns A thru AC. The number of rows can vary. I've sorted the table by column B (Store #). These are the steps I can't figure out: I want to...

www.mrexcel.com

 

[ishanalikhan]

Hi and welcome to MrExcel.

I'm not sure what data you want to copy after doing the filter, and where exactly you want to paste it.

Here are a couple of examples to filter with unique values.

VBA code to copy template worksheet according to list, list has duplicates

Hi All new member here! I have a situation where i need to copy a template tab multiple times, and name these new tabs as per a pre-defined list. I found the below code on this forum Sub makeSheets() Dim sh1 As Worksheet, sh2 As Worksheet, c As Range Set sh1 = Sheets("Template") Set sh2 =...

www.mrexcel.com

Copy data to multiple sheets when store # changes & Name sheet

Summary: I have a spreadsheet with multiple store performance metrics on it and need to break them out to separate tabs. The table has data in columns A thru AC. The number of rows can vary. I've sorted the table by column B (Store #). These are the steps I can't figure out: I want to...

www.mrexcel.com

Hi DanteAmor,

Thank you for the reply. I'm attaching a couple of sheets - Data and Metrics. Data contains my raw data and Metrics is something I'm looking for. Hopefully, this will make my question clearer.

Thank you.

Data:

Template.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1	Date In	Time In	Date Out	Time Out	Item Code	Item Category	Item Name	SN	Operation Code	Operation Category	Status
2	6/1/2021									Cat 1
3	6/1/2021									Cat 3
4	6/1/2021									Cat 5
5	6/1/2021									Cat 4
6	6/1/2021									Cat 2
7	6/1/2021									Cat 1
8	6/1/2021									Cat 5
9	6/1/2021									Cat 3
10	6/1/2021									Cat 2
11	6/1/2021									Cat 1
12	6/1/2021									Cat 3
13	6/1/2021									Cat 5
14	6/1/2021									Cat 4
15	6/1/2021									Cat 2
16	6/2/2021									Cat 1
17	6/2/2021									Cat 5
18	6/2/2021									Cat 3
19	6/2/2021									Cat 2
20	6/2/2021									Cat 5
21	6/2/2021									Cat 2
22	6/2/2021									Cat 1
23	6/2/2021									Cat 5
24	6/2/2021									Cat 4
25	6/2/2021									Cat 1
26	6/2/2021									Cat 1
27	6/2/2021									Cat 3
28	6/2/2021									Cat 5
29	6/2/2021									Cat 4
30	6/2/2021									Cat 2
31	6/2/2021									Cat 1
32	6/2/2021									Cat 5
33	6/2/2021									Cat 3
34	6/2/2021									Cat 2
35	6/2/2021									Cat 5
36	6/2/2021									Cat 2
37	6/2/2021									Cat 1
38	6/2/2021									Cat 1
39	6/2/2021									Cat 5
40	6/2/2021									Cat 3
41	6/2/2021									Cat 2
42	6/3/2021									Cat 5
43	6/3/2021									Cat 2
44	6/3/2021									Cat 1
45	6/3/2021									Cat 5
46	6/3/2021									Cat 4
47	6/3/2021									Cat 1
48	6/3/2021									Cat 1
49	6/3/2021									Cat 3
50	6/3/2021									Cat 5
51	6/3/2021									Cat 4
52	6/3/2021									Cat 2
53	6/3/2021									Cat 1
54	6/3/2021									Cat 5
55	6/3/2021									Cat 3
56	6/3/2021									Cat 2
57	6/3/2021									Cat 1
58	6/3/2021									Cat 5
59	6/3/2021									Cat 3
60	6/3/2021									Cat 2
61	6/3/2021									Cat 5
62	6/3/2021									Cat 2
63	6/3/2021									Cat 1
64	6/3/2021									Cat 5
65	6/3/2021									Cat 4
66	6/3/2021									Cat 1
67	6/3/2021									Cat 1
68	6/3/2021									Cat 3
69	6/4/2021									Cat 5
70	6/4/2021									Cat 4
71	6/4/2021									Cat 2
72	6/4/2021									Cat 1
73	6/4/2021									Cat 5
74	6/4/2021									Cat 3
75	6/4/2021									Cat 2
76	6/4/2021									Cat 5
77	6/4/2021									Cat 2
78	6/4/2021									Cat 5
79	6/4/2021									Cat 2
80	6/4/2021									Cat 1
81	6/4/2021									Cat 5
82	6/4/2021									Cat 4
83	6/4/2021									Cat 5
84	6/4/2021									Cat 4
85	6/4/2021									Cat 2
86	6/4/2021									Cat 1
87	6/4/2021									Cat 5
88	6/4/2021									Cat 3
89	6/4/2021									Cat 2
90	6/5/2021									Cat 5
91	6/5/2021									Cat 2
92	6/5/2021									Cat 1
93	6/5/2021									Cat 5
94	6/5/2021									Cat 4
95	6/5/2021									Cat 1
96	6/5/2021									Cat 5
97	6/5/2021									Cat 3
98	6/5/2021									Cat 2
99	6/5/2021									Cat 5
100	6/5/2021									Cat 2
101	6/5/2021									Cat 1
102	6/5/2021									Cat 5
103	6/5/2021									Cat 4
104	6/5/2021									Cat 1
105	6/5/2021									Cat 1
106	6/5/2021									Cat 3
107	6/5/2021									Cat 5
108	6/6/2021									Cat 4
109	6/6/2021									Cat 2
110	6/6/2021									Cat 1
111	6/6/2021									Cat 5
112	6/6/2021									Cat 3
113	6/6/2021									Cat 2
114	6/6/2021									Cat 5
115	6/6/2021									Cat 3
116	6/6/2021									Cat 5
117	6/6/2021									Cat 4
118	6/6/2021									Cat 2
119	6/6/2021									Cat 1
120	6/6/2021									Cat 5
121	6/6/2021									Cat 3
122	6/7/2021									Cat 2
123	6/7/2021									Cat 5
124	6/7/2021									Cat 2
125	6/7/2021									Cat 1
126	6/7/2021									Cat 1
127	6/7/2021									Cat 5
128	6/7/2021									Cat 3
129	6/7/2021									Cat 2
130	6/7/2021									Cat 5
131	6/7/2021									Cat 2
132	6/7/2021									Cat 1
133	6/7/2021									Cat 5
134	6/7/2021									Cat 4
135	6/7/2021									Cat 1
136	6/7/2021									Cat 1
137	6/7/2021									Cat 3
138	6/7/2021									Cat 5
139	6/7/2021									Cat 4
140	6/7/2021									Cat 2
141	6/7/2021									Cat 1
142	6/7/2021									Cat 5
143	6/7/2021									Cat 3
144	6/7/2021									Cat 2
145	6/7/2021									Cat 1
146	6/7/2021									Cat 5
147	6/7/2021									Cat 3
148	6/7/2021									Cat 2
149	6/7/2021									Cat 5
150	6/7/2021									Cat 2
151	6/7/2021									Cat 1
152	6/8/2021									Cat 5
153	6/8/2021									Cat 4
154	6/8/2021									Cat 1
155	6/8/2021									Cat 1
156	6/8/2021									Cat 3
157	6/8/2021									Cat 5
158	6/8/2021									Cat 4
159	6/8/2021									Cat 2
160	6/8/2021									Cat 1
161	6/8/2021									Cat 5
162	6/8/2021									Cat 3
163	6/8/2021									Cat 1
164	6/8/2021									Cat 5
165	6/8/2021									Cat 3
166	6/8/2021									Cat 2
167	6/8/2021									Cat 5
168	6/9/2021									Cat 2
169	6/9/2021									Cat 1
170	6/9/2021									Cat 5
171	6/9/2021									Cat 4
172	6/9/2021									Cat 1
173	6/9/2021									Cat 1
174	6/9/2021									Cat 3
175	6/9/2021									Cat 5
176	6/9/2021									Cat 4
177	6/9/2021									Cat 2
178	6/9/2021									Cat 1
179	6/9/2021									Cat 5
180	6/9/2021									Cat 3
181	6/9/2021									Cat 2
182	6/9/2021									Cat 5
183	6/9/2021									Cat 1
184	6/9/2021									Cat 5
185	6/9/2021									Cat 3
186	6/9/2021									Cat 2
187	6/9/2021									Cat 5
188	6/9/2021									Cat 2
189	6/9/2021									Cat 1
190	6/9/2021									Cat 1
191	6/9/2021									Cat 5
192	6/9/2021									Cat 3
193	6/9/2021									Cat 2
194	6/9/2021									Cat 5
195	6/10/2021									Cat 2
196	6/10/2021									Cat 1
197	6/10/2021									Cat 5
198	6/10/2021									Cat 4
199	6/10/2021									Cat 1
200	6/10/2021									Cat 1
201	6/10/2021									Cat 3
202	6/10/2021									Cat 5
203	6/10/2021									Cat 4
204	6/10/2021									Cat 2
205	6/10/2021									Cat 1
206	6/10/2021									Cat 5
207	6/10/2021									Cat 3
208	6/10/2021									Cat 2
209	6/1/2021									Cat 5
Data

Metrics:

Template.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1		6/1/2021	6/2/2021	6/3/2021	6/4/2021	6/5/2021	6/6/2021	6/7/2021	6/8/2021	6/9/2021	6/10/2021
2	Cat 1	3	7	8	3	5	2	8	4	7	4
3	Cat 2	3	6	5	6	3	3	8	2	6	3
4	Cat 3	3	4	4	2	2	3	4	3	4	2
5	Cat 4	2	2	3	3	2	2	2	2	2	2
6	Cat 5	4	7	7	7	6	4	8	5	8	3
Metrics

 

[DanteAmor]

You can achieve that count with a pivot table:

---------------------------------------
But I prepare the macro

---------------------------------------

 

[DanteAmor]

Try the following macro to sort and count the unique values.

Sub Count_Cat()
  Dim dic1 As Object, dic2 As Object, coll As Object
  Dim a As Variant, b As Variant, cat As Variant, fec As Variant
  Dim i As Long, j As Long, k As Long
 
  Set coll = CreateObject("System.Collections.ArrayList")
  Set dic1 = CreateObject("Scripting.Dictionary")
  Set dic2 = CreateObject("Scripting.Dictionary")
  
  a = Sheets("Data").Range("A2", Sheets("Data").Range("J" & Rows.Count).End(3)).Value
  ReDim b(1 To UBound(a, 1), 1 To UBound(a, 1))
 
  For i = 1 To UBound(a, 1)
    coll.Add a(i, 10) & "|" & a(i, 1)
  Next
  coll.Sort
  
  For i = 0 To coll.Count - 1
    cat = Split(coll(i), "|")(0)
    fec = Split(coll(i), "|")(1)
    If Not dic1.exists(cat) Then j = dic1.Count + 1: dic1(cat) = j Else j = dic1(cat)
    If Not dic2.exists(fec) Then k = dic2.Count + 1: dic2(fec) = k Else k = dic2(fec)
    b(j, k) = b(j, k) + 1
  Next
  
  With Sheets("Metrics")
    .Range("A2").Resize(dic1.Count, 1).Value = Application.Transpose(dic1.keys)
    .Range("B1").Resize(1, dic2.Count).Value = dic2.keys
    .Range("B2").Resize(dic1.Count, dic2.Count).Value = b
  End With
End Sub

 

[ishanalikhan]

Try the following macro to sort and count the unique values.

Sub Count_Cat()
  Dim dic1 As Object, dic2 As Object, coll As Object
  Dim a As Variant, b As Variant, cat As Variant, fec As Variant
  Dim i As Long, j As Long, k As Long
 
  Set coll = CreateObject("System.Collections.ArrayList")
  Set dic1 = CreateObject("Scripting.Dictionary")
  Set dic2 = CreateObject("Scripting.Dictionary")
 
  a = Sheets("Data").Range("A2", Sheets("Data").Range("J" & Rows.Count).End(3)).Value
  ReDim b(1 To UBound(a, 1), 1 To UBound(a, 1))
 
  For i = 1 To UBound(a, 1)
    coll.Add a(i, 10) & "|" & a(i, 1)
  Next
  coll.Sort
 
  For i = 0 To coll.Count - 1
    cat = Split(coll(i), "|")(0)
    fec = Split(coll(i), "|")(1)
    If Not dic1.exists(cat) Then j = dic1.Count + 1: dic1(cat) = j Else j = dic1(cat)
    If Not dic2.exists(fec) Then k = dic2.Count + 1: dic2(fec) = k Else k = dic2(fec)
    b(j, k) = b(j, k) + 1
  Next
 
  With Sheets("Metrics")
    .Range("A2").Resize(dic1.Count, 1).Value = Application.Transpose(dic1.keys)
    .Range("B1").Resize(1, dic2.Count).Value = dic2.keys
    .Range("B2").Resize(dic1.Count, dic2.Count).Value = b
  End With
End Sub

Thank you, and pardon me for the late response. This is certainly helpful.

I want to modify this code to select the data between a range of dates provided by the user. Any pointers?

I really appreciate any help you can provide.

 

[DanteAmor]

I highly recommend using the pivot table tool.
You can filter by date, for example, from and to, between, until, select by month or several days.