Frequency of duplicate dates among a range of data

[picartman]

Hello, I am trying to figure out if there is a way I can build a frequency table out of this raw data. I have a set of dates with duplicates, I need to figure out how many times a certain date appears.

Here is the raw data:
1-Mar
1-Mar
1-Mar
2-Mar
2-Mar
2-Mar
2-Mar
3-Mar
3-Mar
3-Mar
3-Mar
5-Mar
5-Mar

For example, March 1 would be 3, March 2 is 4, March 3 is 4, and March 5 is 2. This is the table that I would like to see so I can make a chart out of it:

Dates Frequency
1-Mar 3
2-Mar 4
3-Mar 4
5-Mar 2

The problem I'm facing is that I have about a year's worth of data I need to build a frequency chart out of. Counting it one by one would take a very long time. I was hoping there is a function I could use that would build a frequency chart for me. I'm on Excel 2010 also if that makes a difference.

I appreciate any help, thanks for you time.

[AliGW]

Maybe this?

Excel 2016 (Windows) 32 bit

	A	B	C	D
1	01-Mar		Date	Count
2	01-Mar		01-Mar	3
3	01-Mar		02-Mar	4
4	02-Mar		03-Mar	4
5	02-Mar		05-Mar	2
6	02-Mar
7	02-Mar
8	03-Mar
9	03-Mar
10	03-Mar
11	03-Mar
12	05-Mar
13	05-Mar

Sheet: Sheet1

Excel 2016 (Windows) 32 bit

	C	D
2	=IFERROR(INDEX($A$1:$A$13,MATCH(0,INDEX(COUNTIF($C$1:C1,$A$1:$A$13),0,0),0)),"")	=IF(C2="","",COUNTIF($A$1:$A$13,C2))

Sheet: Sheet1

[picartman]

Hello, thanks for the reply. I'm trying to figure out the formula you posted, I pasted it into the work book but it just returned the number 43160, not sure what I'm doing wrong. I forgot to mention I'm on Excel 2010 if that makes a difference.

[FlameRetired]

picartman if you are not aware of it dates (and times) are numbers. Dates are numbered from 1/1/1900 being day 1. 43160 is that many days from 1/1/1900. The formatting is cosmetic. If you format that dd - mmm it is 01 - Mar


An alternative means to return unique dates with a helper column in B and this formula

=IF(COUNTIF($A$1:A1,A1)=1,ROWS($A$1:$A1),"")

Then this non-array in C2 and down.

=INDEX($A$1:$A$13,SMALL($B$1:$B$13,ROWS($A$2:$A2)))

	A	B	C	D
1	01 - Mar	1	Date	Count
2	01 - Mar		01 - Mar	3
3	01 - Mar		02 - Mar	4
4	02 - Mar	4	03 - Mar	4
5	02 - Mar		05 - Mar	2
6	02 - Mar
7	02 - Mar
8	03 - Mar	8
9	03 - Mar
10	03 - Mar
11	03 - Mar
12	05 - Mar	12
13	05 - Mar

[AliGW]

Just copy and paste the FORMATTING of the date cell to your output list, as has already been mentioned.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.