finding the most repeated dates in a range

R

redahussien

New Member
Joined
Feb 15, 2016
Messages
3
hi all,
can you help in finding the most repeated dates in a range a1:d4

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD][TABLE="width: 75"]
<tbody>[TR]
[TD="class: xl65, width: 75, align: center"]2/2/2009[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 75"]
<tbody>[TR]
[TD="class: xl65, width: 75, align: center"]2/2/2009[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 75"]
<tbody>[TR]
[TD="class: xl65, width: 75, align: center"]2/2/2009[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 75"]
<tbody>[TR]
[TD="class: xl65, width: 75, align: center"]2/2/2010[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD][TABLE="width: 75"]
<tbody>[TR]
[TD="class: xl65, width: 75, align: center"]4/21/2009[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 75"]
<tbody>[TR]
[TD="class: xl65, width: 75, align: center"]2/1/2009[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 75"]
<tbody>[TR]
[TD="class: xl65, width: 75, align: center"]8/8/2009[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 75"]
<tbody>[TR]
[TD="class: xl65, width: 75, align: center"]2/12/2009[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD][TABLE="width: 75"]
<tbody>[TR]
[TD="class: xl65, width: 75, align: center"]2/2/2009[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 75"]
<tbody>[TR]
[TD="class: xl65, width: 75, align: center"]2/2/2009[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 75"]
<tbody>[TR]
[TD="class: xl65, width: 75, align: center"]4/21/2009[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 75"]
<tbody>[TR]
[TD="class: xl65, width: 75, align: center"]2/12/2009[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD][TABLE="width: 75"]
<tbody>[TR]
[TD="class: xl65, width: 75, align: center"]8/8/2009[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 75"]
<tbody>[TR]
[TD="class: xl65, width: 75, align: center"]2/12/2009[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 75"]
<tbody>[TR]
[TD="class: xl65, width: 75, align: center"]2/2/2009[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 75"]
<tbody>[TR]
[TD="class: xl65, width: 75, align: center"]2/2/2009[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

i want to list all the repeated values from the most to the least :

dates repeated
2/2/2009 7
2/12/2009 3
2/2/2009 3
4/21/2009 2
8/8/2009 2
2/1/2009 1
2/2/2010 1

note that the columns dates and repeated are formula generated , i want to extract them and count the repetition. cos i have a large list of dates.
​
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
Sort by date Sort by votes
Here is one way, using VBA
- run from sheet containing dates

Code: 
Sub CountDates()
    Dim cel As Range, rng As Range, ws As Worksheet, r As Long
    Set rng = ActiveSheet.Range("A1").CurrentRegion              '[COLOR=#006400]amend range if necessary to match your data[/COLOR]
    
    Set ws = Worksheets.Add
    For Each cel In rng
        r = r + 1
        ws.Cells(r, 1).Resize(, 2) = Array(cel, WorksheetFunction.CountIf(rng, cel))
    Next cel
    With ws.Range("A:B")
        .RemoveDuplicates Columns:=Array(1, 2), Header:=xlNo
        .Sort Key1:=Range("B1"), Order1:=xlDescending, Header:=xlNo
        .Resize(, 1).NumberFormat = rng.Cells(1, 1).NumberFormat
    End With
End Sub


Sheet with dates starting in A1

Excel 2016 (Windows) 32 bit
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]
A​
[/th][th]
B​
[/th][th]
C​
[/th][th]
D​
[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
1​
[/td][td]
29/08/2019​
[/td][td]
29/08/2019​
[/td][td]
30/08/2019​
[/td][td]
31/08/2019​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
2​
[/td][td]
01/09/2019​
[/td][td]
28/08/2019​
[/td][td]
27/08/2019​
[/td][td]
01/09/2019​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
3​
[/td][td]
04/09/2019​
[/td][td]
01/09/2019​
[/td][td]
29/08/2019​
[/td][td]
28/08/2019​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
4​
[/td][td]
02/09/2019​
[/td][td]
29/08/2019​
[/td][td]
01/09/2019​
[/td][td]
27/08/2019​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
5​
[/td][td]
02/09/2019​
[/td][td]
27/08/2019​
[/td][td]
28/08/2019​
[/td][td]
29/08/2019​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
6​
[/td][td]
01/09/2019​
[/td][td]
30/08/2019​
[/td][td]
01/09/2019​
[/td][td]
01/09/2019​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
7​
[/td][td]
04/09/2019​
[/td][td]
31/08/2019​
[/td][td]
31/08/2019​
[/td][td]
05/09/2019​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
8​
[/td][td]
31/08/2019​
[/td][td]
29/08/2019​
[/td][td]
05/09/2019​
[/td][td]
03/09/2019​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
9​
[/td][td]
27/08/2019​
[/td][td]
03/09/2019​
[/td][td]
27/08/2019​
[/td][td]
04/09/2019​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
10​
[/td][td]
28/08/2019​
[/td][td]
27/08/2019​
[/td][td]
05/09/2019​
[/td][td]
03/09/2019​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
11​
[/td][td]
03/09/2019​
[/td][td]
01/09/2019​
[/td][td]
29/08/2019​
[/td][td]
28/08/2019​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
12​
[/td][td]
30/08/2019​
[/td][td]
31/08/2019​
[/td][td]
27/08/2019​
[/td][td]
03/09/2019​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
13​
[/td][td]
05/09/2019​
[/td][td]
30/08/2019​
[/td][td]
02/09/2019​
[/td][td]
30/08/2019​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
14​
[/td][td]
29/08/2019​
[/td][td]
02/09/2019​
[/td][td]
30/08/2019​
[/td][td]
03/09/2019​
[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet12[/td][/tr][/table]

Output in new sheet (from most to least)

Excel 2016 (Windows) 32 bit
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]
A​
[/th][th]
B​
[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
1​
[/td][td]
29/08/2019​
[/td][td]
8​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
2​
[/td][td]
01/09/2019​
[/td][td]
8​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
3​
[/td][td]
27/08/2019​
[/td][td]
7​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
4​
[/td][td]
30/08/2019​
[/td][td]
6​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
5​
[/td][td]
03/09/2019​
[/td][td]
6​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
6​
[/td][td]
31/08/2019​
[/td][td]
5​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
7​
[/td][td]
28/08/2019​
[/td][td]
5​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
8​
[/td][td]
02/09/2019​
[/td][td]
4​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
9​
[/td][td]
05/09/2019​
[/td][td]
4​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
10​
[/td][td]
04/09/2019​
[/td][td]
3​
[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet19[/td][/tr][/table]
 
Upvote 0
it works perfect Man , thanks a Million.
ill try to modify it to get the top 5 , if it does not work with me ill get back to you.
thanks in advance.
 
Upvote 0
If you simply want to be left with the first 5 values, then add this as the last line

Code: 
ws.Range("A6").Resize(Rows.Count - 5).EntireRow.Delete
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Solved
Formula to 2-way look up a unique identifier and a date, then take the highest number from another column
Replies
3
Views
363
MrsFraser07
M
A
  • Question Question
Need to convert value depending on currency and date
Replies
2
Views
505
deLockloire
D
N
  • Solved
Checking Dates
Replies
1
Views
315
Noxqss38242
N
D
  • Solved
Repeat Range of Cells N times with each item in range repeating X times
Replies
5
Views
431
Cubist
Cubist
W
  • Question Question
[Excel 365] Maximizing unique combinations of words - No repeats between any two words
Replies
4
Views
404
waffle
W

Forum statistics

Threads
1,224,823
Messages
6,181,180
Members
453,021
Latest member
Justyna P

Share this page

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
Back
Top