2

I have some data as such:

User Date        
001  1/1/2000     
002  1/25/2000    
003  2/2/2005     
004  2/5/2005     
005  12/24/2008

I would like to find out, in each month, how many unique users are present?

So in my case, there would be 2 users present for Jan 2000, 2 users for Feb 2005, and 1 user for Dec 2008.

Towards solving this, I have tried using a PivotTable, however, I can't find the appropriate way to group the data by month. So I suspect I need to use the equation bar, but I'm not sure how to use a Group By function.

2
  • What version of Excel are you using? I'm using Excel 2010. I could achieve your requirement just by applying filter to the data
    – Prasanna
    Aug 24, 2014 at 6:56
  • I am using Excel 2010 as well. Aug 24, 2014 at 7:35

3 Answers 3

2

Select the top rows of the data (say username and date row) and apply filter to your data as shown below.
Applying filter
Then after you have applied filter your data will appear like this.
Filter applied

Now click on the drop down arrow on the date column and it will give you the options to select year and month wise as shown below
Filter selection

Edit 1:

Make a copy of your file in the first place. Further, if you want to remove the duplicates of the user column and count, in Excel, Select Data tab and within Data tools group click on Remove duplicates. Uncheck the remaining columns and keep only "USER" and click OK. Remove duplicates
This action will delete the duplicate records found in USER column. Now you have only unique records. You can do a pivot table and find out the count.

0
0

You can add a new column and use this equation to unify days in a month:

=[DATECELL]-DAY([DATE_CELL])+1

Then apply a PivotTable to this column.

0
0

Add a column to your data: =YEAR(<date cell>) & MONTH(<date cell>). Then try the pivot table.

Alternatively, format the date column using the cell properties as a custom format: 'yyyy-mm'. And then pivot the table.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .