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

ishanalikhan

New Member
Joined
Jul 28, 2021
Messages
4
Office Version
  1. 365
Platform
  1. Windows
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:

VBA Code:
.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.
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
13,924
Office Version
  1. 2007
Platform
  1. Windows
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.

 

ishanalikhan

New Member
Joined
Jul 28, 2021
Messages
4
Office Version
  1. 365
Platform
  1. Windows
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.

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
ABCDEFGHIJK
1Date InTime InDate OutTime OutItem CodeItem CategoryItem NameSNOperation CodeOperation CategoryStatus
26/1/2021Cat 1
36/1/2021Cat 3
46/1/2021Cat 5
56/1/2021Cat 4
66/1/2021Cat 2
76/1/2021Cat 1
86/1/2021Cat 5
96/1/2021Cat 3
106/1/2021Cat 2
116/1/2021Cat 1
126/1/2021Cat 3
136/1/2021Cat 5
146/1/2021Cat 4
156/1/2021Cat 2
166/2/2021Cat 1
176/2/2021Cat 5
186/2/2021Cat 3
196/2/2021Cat 2
206/2/2021Cat 5
216/2/2021Cat 2
226/2/2021Cat 1
236/2/2021Cat 5
246/2/2021Cat 4
256/2/2021Cat 1
266/2/2021Cat 1
276/2/2021Cat 3
286/2/2021Cat 5
296/2/2021Cat 4
306/2/2021Cat 2
316/2/2021Cat 1
326/2/2021Cat 5
336/2/2021Cat 3
346/2/2021Cat 2
356/2/2021Cat 5
366/2/2021Cat 2
376/2/2021Cat 1
386/2/2021Cat 1
396/2/2021Cat 5
406/2/2021Cat 3
416/2/2021Cat 2
426/3/2021Cat 5
436/3/2021Cat 2
446/3/2021Cat 1
456/3/2021Cat 5
466/3/2021Cat 4
476/3/2021Cat 1
486/3/2021Cat 1
496/3/2021Cat 3
506/3/2021Cat 5
516/3/2021Cat 4
526/3/2021Cat 2
536/3/2021Cat 1
546/3/2021Cat 5
556/3/2021Cat 3
566/3/2021Cat 2
576/3/2021Cat 1
586/3/2021Cat 5
596/3/2021Cat 3
606/3/2021Cat 2
616/3/2021Cat 5
626/3/2021Cat 2
636/3/2021Cat 1
646/3/2021Cat 5
656/3/2021Cat 4
666/3/2021Cat 1
676/3/2021Cat 1
686/3/2021Cat 3
696/4/2021Cat 5
706/4/2021Cat 4
716/4/2021Cat 2
726/4/2021Cat 1
736/4/2021Cat 5
746/4/2021Cat 3
756/4/2021Cat 2
766/4/2021Cat 5
776/4/2021Cat 2
786/4/2021Cat 5
796/4/2021Cat 2
806/4/2021Cat 1
816/4/2021Cat 5
826/4/2021Cat 4
836/4/2021Cat 5
846/4/2021Cat 4
856/4/2021Cat 2
866/4/2021Cat 1
876/4/2021Cat 5
886/4/2021Cat 3
896/4/2021Cat 2
906/5/2021Cat 5
916/5/2021Cat 2
926/5/2021Cat 1
936/5/2021Cat 5
946/5/2021Cat 4
956/5/2021Cat 1
966/5/2021Cat 5
976/5/2021Cat 3
986/5/2021Cat 2
996/5/2021Cat 5
1006/5/2021Cat 2
1016/5/2021Cat 1
1026/5/2021Cat 5
1036/5/2021Cat 4
1046/5/2021Cat 1
1056/5/2021Cat 1
1066/5/2021Cat 3
1076/5/2021Cat 5
1086/6/2021Cat 4
1096/6/2021Cat 2
1106/6/2021Cat 1
1116/6/2021Cat 5
1126/6/2021Cat 3
1136/6/2021Cat 2
1146/6/2021Cat 5
1156/6/2021Cat 3
1166/6/2021Cat 5
1176/6/2021Cat 4
1186/6/2021Cat 2
1196/6/2021Cat 1
1206/6/2021Cat 5
1216/6/2021Cat 3
1226/7/2021Cat 2
1236/7/2021Cat 5
1246/7/2021Cat 2
1256/7/2021Cat 1
1266/7/2021Cat 1
1276/7/2021Cat 5
1286/7/2021Cat 3
1296/7/2021Cat 2
1306/7/2021Cat 5
1316/7/2021Cat 2
1326/7/2021Cat 1
1336/7/2021Cat 5
1346/7/2021Cat 4
1356/7/2021Cat 1
1366/7/2021Cat 1
1376/7/2021Cat 3
1386/7/2021Cat 5
1396/7/2021Cat 4
1406/7/2021Cat 2
1416/7/2021Cat 1
1426/7/2021Cat 5
1436/7/2021Cat 3
1446/7/2021Cat 2
1456/7/2021Cat 1
1466/7/2021Cat 5
1476/7/2021Cat 3
1486/7/2021Cat 2
1496/7/2021Cat 5
1506/7/2021Cat 2
1516/7/2021Cat 1
1526/8/2021Cat 5
1536/8/2021Cat 4
1546/8/2021Cat 1
1556/8/2021Cat 1
1566/8/2021Cat 3
1576/8/2021Cat 5
1586/8/2021Cat 4
1596/8/2021Cat 2
1606/8/2021Cat 1
1616/8/2021Cat 5
1626/8/2021Cat 3
1636/8/2021Cat 1
1646/8/2021Cat 5
1656/8/2021Cat 3
1666/8/2021Cat 2
1676/8/2021Cat 5
1686/9/2021Cat 2
1696/9/2021Cat 1
1706/9/2021Cat 5
1716/9/2021Cat 4
1726/9/2021Cat 1
1736/9/2021Cat 1
1746/9/2021Cat 3
1756/9/2021Cat 5
1766/9/2021Cat 4
1776/9/2021Cat 2
1786/9/2021Cat 1
1796/9/2021Cat 5
1806/9/2021Cat 3
1816/9/2021Cat 2
1826/9/2021Cat 5
1836/9/2021Cat 1
1846/9/2021Cat 5
1856/9/2021Cat 3
1866/9/2021Cat 2
1876/9/2021Cat 5
1886/9/2021Cat 2
1896/9/2021Cat 1
1906/9/2021Cat 1
1916/9/2021Cat 5
1926/9/2021Cat 3
1936/9/2021Cat 2
1946/9/2021Cat 5
1956/10/2021Cat 2
1966/10/2021Cat 1
1976/10/2021Cat 5
1986/10/2021Cat 4
1996/10/2021Cat 1
2006/10/2021Cat 1
2016/10/2021Cat 3
2026/10/2021Cat 5
2036/10/2021Cat 4
2046/10/2021Cat 2
2056/10/2021Cat 1
2066/10/2021Cat 5
2076/10/2021Cat 3
2086/10/2021Cat 2
2096/1/2021Cat 5
Data


Metrics:

Template.xlsx
ABCDEFGHIJK
16/1/20216/2/20216/3/20216/4/20216/5/20216/6/20216/7/20216/8/20216/9/20216/10/2021
2Cat 13783528474
3Cat 23656338263
4Cat 33442234342
5Cat 42233222222
6Cat 54777648583
Metrics
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
13,924
Office Version
  1. 2007
Platform
  1. Windows
You can achieve that count with a pivot table:

1627583388041.png


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

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

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
13,924
Office Version
  1. 2007
Platform
  1. Windows

ADVERTISEMENT

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

VBA Code:
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

New Member
Joined
Jul 28, 2021
Messages
4
Office Version
  1. 365
Platform
  1. Windows
Try the following macro to sort and count the unique values.

VBA Code:
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

Well-known Member
Joined
Dec 3, 2018
Messages
13,924
Office Version
  1. 2007
Platform
  1. Windows
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.
1631717452432.png
 

Forum statistics

Threads
1,147,823
Messages
5,743,409
Members
423,792
Latest member
travisds

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top