Counting Unique Values

[jakeman]

Hello Excel gurus -

I have a spreadsheet with 3 columns that I am looking to count unique values from: User, Task, and DateRange (which I have built as named ranges, btw). What I am trying to do is count the number of unique instances where a user performed a task in a given month.

For example, Joe Smith performed a Task called "Business Analysis" in July. Now, I have the following formula built:

=(SUMPRODUCT((User="Joe Smith")*(Task="Business Analysis")*(MONTH(DateRange)=7)))

The result I get for just this user is 43. Because Joe performed the same task more than one time, it returns all of the instances but I am only interested in knowing the unique instances, in this case just 1 instance. I have 54 users that I am looking to analyze and determine which users performed a certain task in a given month. When I look at all my team, I see that in July I have 40 out of 54 users who performed the task "Business Analysis" but that is a very manual process for me to do and I have 8 other tasks that I am trying to analyze in the same manner. How can I construct a formula in Excel to evaluate unique instances of a task performed by a user in a single month?

Thanks.

 

[Aladin Akyurek]

Hello Excel gurus -

I have a spreadsheet with 3 columns that I am looking to count unique values from: User, Task, and DateRange (which I have built as named ranges, btw). What I am trying to do is count the number of unique instances where a user performed a task in a given month.

For example, Joe Smith performed a Task called "Business Analysis" in July. Now, I have the following formula built:

=(SUMPRODUCT((User="Joe Smith")*(Task="Business Analysis")*(MONTH(DateRange)=7)))

The result I get for just this user is 43. Because Joe performed the same task more than one time, it returns all of the instances but I am only interested in knowing the unique instances, in this case just 1 instance. I have 54 users that I am looking to analyze and determine which users performed a certain task in a given month. When I look at all my team, I see that in July I have 40 out of 54 users who performed the task "Business Analysis" but that is a very manual process for me to do and I have 8 other tasks that I am trying to analyze in the same manner. How can I construct a formula in Excel to evaluate unique instances of a task performed by a user in a single month?

Thanks.

Your specs are not very clear...

If you want a unique/distinct of tasks a user has carried out in a particular month/year...
Control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF(Task<>"",IF(User="Joe Smith",
  IF(DateRange-DAY(DateRange)+1=DATE(2012,7,1),
   MATCH("~"&Task,Task&"",0)))),
    ROW(Task)-ROW(INDEX(Task,1,1))+1),1))

If this is not what you want, please elaborate by means of a very tiny sample.

 

[jakeman]

Aladin - thanks for your reply. Let me start over as I realized my first approach wasn't really evaluating what I needed.

1) I have 9 tasks that I am trying to count:

1. Business Analysis
2. Business Implementation
3. Design Analysis
4. Operational Readiness
5. Project Management
6. Quality Inspection
7. Solutioning Analysis
8. Test Execution
9. Test Planning

2) I have approximately 55 people on a team who may or may not perform any of these tasks in a given month.
3) I want to evaluate the number of unique instances that one of the 55 people performed any of the tasks listed above.
4) My raw data reads like this (just a quick sample):

User	Task	Date Performed
Joe Smith	Business Analysis	7/01/2012
Paul Jones	Business Analysis	7/01/2012
Jim White	Business Analysis	7/01/2012
Sue Holmes	Business Analysis	7/01/2012
Joe Smith	Business Analysis	7/02/2012
Paul Jones	Business Analysis	7/02/2012
Jim White	Business Analysis	7/02/2012
Sue Holmes	Business Analysis	7/02/2012

<TBODY>
</TBODY>

5) What I am trying to arrive at is to count of the number of unique instances of the task Business Analysis being performed, based upon the users and the date range. So, for example, in July I have 4 unique occurences of Business Analysis in July:

1. Joe Smith: performed Business Analysis 2x in July but I only count 1
2. Paul Jones: same as Joe Smith
3. Jim White: same as Joe Smith
4. Sue Holmes: same as Joe Smith

I tried my best to explain my specs. Please let me know if you have any follow-up questions.

Thanks for taking a crack at it.

Jake

 

[Aladin Akyurek]

Aladin - thanks for your reply. Let me start over as I realized my first approach wasn't really evaluating what I needed.

1) I have 9 tasks that I am trying to count:

1. Business Analysis
2. Business Implementation
3. Design Analysis
4. Operational Readiness
5. Project Management
6. Quality Inspection
7. Solutioning Analysis
8. Test Execution
9. Test Planning

2) I have approximately 55 people on a team who may or may not perform any of these tasks in a given month.
3) I want to evaluate the number of unique instances that one of the 55 people performed any of the tasks listed above.
4) My raw data reads like this (just a quick sample):

User	Task	Date Performed
Joe Smith	Business Analysis	7/01/2012
Paul Jones	Business Analysis	7/01/2012
Jim White	Business Analysis	7/01/2012
Sue Holmes	Business Analysis	7/01/2012
Joe Smith	Business Analysis	7/02/2012
Paul Jones	Business Analysis	7/02/2012
Jim White	Business Analysis	7/02/2012
Sue Holmes	Business Analysis	7/02/2012

<tbody>
</tbody>

5) What I am trying to arrive at is to count of the number of unique instances of the task Business Analysis being performed, based upon the users and the date range. So, for example, in July I have 4 unique occurences of Business Analysis in July:

1. Joe Smith: performed Business Analysis 2x in July but I only count 1
2. Paul Jones: same as Joe Smith
3. Jim White: same as Joe Smith
4. Sue Holmes: same as Joe Smith

I tried my best to explain my specs. Please let me know if you have any follow-up questions.

Thanks for taking a crack at it.

Jake

I guess your point (5) specifies the calculation you need. If so, I already provided this one, as shown below.
Not sure whether (3) is also something that specifies a calculation...

User	Task	Date Performed			Business Analysis		User	7/1/2012
Joe Smith	Business Analysis	7/1/2012			Business Implementation		Joe Smith	1
Paul Jones	Business Analysis	7/1/2012			Design Analysis		Paul Jones	1
Jim White	Business Analysis	7/1/2012			Operational Readiness		Jim White	1
Sue Holmes	Business Analysis	7/1/2012			Project Management		Sue Holmes	1
Joe Smith	Business Analysis	7/2/2012			Quality Inspection
Paul Jones	Business Analysis	7/2/2012			Solutioning Analysis
Jim White	Business Analysis	7/2/2012			Test Execution
Sue Holmes	Business Analysis	7/2/2012			Test Planning

<tbody>
</tbody>

A2:A9 is named User; B2:B9 Task; C2:C9 Date; and F1:F9 TaskList.

H2:H5 houses a set of users of interest.

I1, just enter:

=DATE(2012,7,1)

which specifies the month/year July 2012. You can directly enter in this cell 7/1/2012 instead of a formula which creates that date.

I2, control+shift+enter, not just enter, and copy down:

=SUM(IF(FREQUENCY(IF(Task<>"",IF(User=$H2,
  IF(Date-DAY(Date)+1=I$1,
   MATCH("~"&Task,Task&"",0)))),
    ROW(Task)-ROW(INDEX(Task,1,1))+1),1))

Would you assess the foregoing? Also, please elaborate on your (3) if it's a calculation you need.

 

[Fazza]

hi, Jake

A pivot table is another approach - it might be preferable if you have a large amount of data.

The solution is specific to the question and modifies the dataset - using SQL - between the source data and the pivot table. The SQL might be

SELECT DISTINCT User, Task, Year([Date Performed])+Month([Date Performed])/100 AS [MyDate]
FROM MyData

It is assumed that the source data has a normal defined name 'MyData' & the file is saved. From a new workbook start the pivot table wizard, ALT-D-P, choose external data source, Get Data, Excel files, OK, browse for your file, OK, see the defined name 'MyData', select it, proceed into MS Query and edit the SQL to be as above. 'Open door' to exit MS Query and then finish. Move the fields into the pivot table. Set the data field to be a count, not sum.

Or you could do the same sort of thing with a query table which is fairly similar - a database type approach without formulas.

hth

 

[jakeman]

hi, Jake

A pivot table is another approach - it might be preferable if you have a large amount of data.

The solution is specific to the question and modifies the dataset - using SQL - between the source data and the pivot table. The SQL might be

SELECT DISTINCT User, Task, Year([Date Performed])+Month([Date Performed])/100 AS [MyDate]
FROM MyData

It is assumed that the source data has a normal defined name 'MyData' & the file is saved. From a new workbook start the pivot table wizard, ALT-D-P, choose external data source, Get Data, Excel files, OK, browse for your file, OK, see the defined name 'MyData', select it, proceed into MS Query and edit the SQL to be as above. 'Open door' to exit MS Query and then finish. Move the fields into the pivot table. Set the data field to be a count, not sum.

Or you could do the same sort of thing with a query table which is fairly similar - a database type approach without formulas.

hth

Fazza - this was very helpful and a distinctive approach to solving my problem. Worked perfectly.

Thank you also, Aladin, for taking the time to respond.

Cheers.

 

[jakeman]

hi, Jake

A pivot table is another approach - it might be preferable if you have a large amount of data.

The solution is specific to the question and modifies the dataset - using SQL - between the source data and the pivot table. The SQL might be

SELECT DISTINCT User, Task, Year([Date Performed])+Month([Date Performed])/100 AS [MyDate]
FROM MyData

It is assumed that the source data has a normal defined name 'MyData' & the file is saved. From a new workbook start the pivot table wizard, ALT-D-P, choose external data source, Get Data, Excel files, OK, browse for your file, OK, see the defined name 'MyData', select it, proceed into MS Query and edit the SQL to be as above. 'Open door' to exit MS Query and then finish. Move the fields into the pivot table. Set the data field to be a count, not sum.

Or you could do the same sort of thing with a query table which is fairly similar - a database type approach without formulas.

hth

Hello Fazza - I have worked with the code you gave me and have tried to tweak it to my liking but I am having a problem. I'd prefer to use a real date format to roll up the months as opposed to String.

I tried using the Format function in my SQL but I keep getting the error: [Microsoft][ODBC Excel Driver] Too few parameters. Expected 1.

I believe it is because the Format function is not supported in SQL for Excel. This is my code:

SELECT DISTINCT User, `Map Task`, Format([DateMod],"MM-YYYY") AS [MyDate]
FROM Data_Range
WHERE (`Category Type`='Capital Project')

The reason I am trying to do this is so that I can eventuallyroll up the months into Quarters, which is better for my purposes. Do you know of another way to get DateMod to roll up to the month?

Thanks.

Jake

 

[Fazza]

hi, Jake

Addressing your comments.

You "prefer to use a real data format to roll up the months as opposed to String." I didn't use string, I used a decimal number. Your SQL explicitly converts to string. This is a confusing to me but I think a non-issue; or trivial anyway.

Format function is OK.

The error 'too few parameter' indicates a field name in the SQL doesn't exist in the source data. I don't know what your field names are. Earlier in the thread they were [User], [Task], [Date Performed]. Your current SQL refers to fields [User], [Map Task], [DateMod] & [Category Type]. On the face of it, there should be multilple fields unknown to the SQL. Whatever the field names, it should be simple to resolve the current problem.

It should be as simple as aligning the field names used in the SQL with your actual field names. If you don't sort this out readily, please post the field names & I'll have a go at editing the SQL.

regards, Fazza