INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Jobs from Indeed

EXCEL: Distiinct count of ID with multiple conditions

EXCEL: Distiinct count of ID with multiple conditions

EXCEL: Distiinct count of ID with multiple conditions

(OP)
I have an Excel dataset comprising around a dozen columns and 900 rows. The REF_ID (col B) contains duplicates due to multiple values in a number of columns (although not the columns that specifically interest me in this exercise).

I need to get a Distinct Count of the REF_IDs (Col B) where Col A (RPT_TYPE) = "IDR_RECD" and Col E (date/time) is during April 2016 (dataset includes data from April to September).

Obviously I can achieve what I want with Pivot Tables but I would really like to get a formula that will do it for me as it will assist with the broader report requirements (and hopefully broaden my Excel skills in the process).

I have googled this extensively, but the results aren't quite what I need and I am struggling to apply them to my specific requirements, or really understand the code provided. I suspect my problem is simply one of "old dog / new trick".

I know of a number of Excel experts here who will be able to provide the solution I am looking for.

Any and all assistance greatly appreciated.

Regards

Pete

RE: EXCEL: Distiinct count of ID with multiple conditions

Hi,

Would be nice to have some test data and what result you expect from the given test set.

I doubt that a FORMULA will yield the results that I THINK that you're expecting. That's why we need an example AND your expected results.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: EXCEL: Distiinct count of ID with multiple conditions

Here's a solution to the question that I THINK you are asking.

Here's my test set

RPT_TYPE	REF_ID	colc	cold	date/time
IDR_RECD	AA			4/1/2016
IDR_RECD	BB			4/4/2016
IDR_RECD	BB			4/7/2016
IDR_RECD	AA			4/10/2016
        	AA			4/13/2016
        	CC			4/16/2016
        	BB			4/19/2016
        	CC			4/22/2016
        	AA			4/25/2016
        	BB			4/28/2016
        	CC			5/1/2016
IDR_RECD	AA			5/4/2016
IDR_RECD	BB			5/7/2016
 

So I add a helper column YYYYMM with this formula...

F2: =IF(AND(TEXT(date_time,"yyyymm")="201604",RPT_TYPE="IDR_RECD"),REF_ID,"")

...and here are my results...

RPT_TYPE	REF_ID	colc	cold	date/time	YYYYMM
IDR_RECD	AA			4/1/2016	AA
IDR_RECD	BB			4/4/2016	BB
IDR_RECD	BB			4/7/2016	BB
IDR_RECD	AA			4/10/2016	AA
        	AA			4/13/2016	
        	CC			4/16/2016	
        	BB			4/19/2016	
        	CC			4/22/2016	
        	AA			4/25/2016	
        	BB			4/28/2016	
        	CC			5/1/2016	
IDR_RECD	AA			5/4/2016	
IDR_RECD	BB			5/7/2016	
 

Now the formula to calculate the number of unique occurrences...

=SUM(IF(FREQUENCY(IF(YYYYMM<>"", MATCH(YYYYMM,YYYYMM,0)),ROW(YYYYMM)-ROW(F2)+1),1))

...entered as an ArrAY FormULA (SHIFT+ctrl+ENTER)
My result is 2.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: EXCEL: Distiinct count of ID with multiple conditions

(OP)
Thanks Skip. I really appreciate you taking the time to assist.

Sample data:

RPT_TYPE	REF_ID	ISSUE_TYPE	ISSUE			DATE_TIME
IDR_CLOSE	453956	Process 	Biased Process		29/09/2016 13:34
IDR_CLOSE	453956	Process 	Disagree with decision 	29/09/2016 13:34
IDR_CLOSE	454408	Process 	Unable to understand 	27/09/2016 18:48
IDR_CLOSE	454570	Process 	Delay			22/09/2016 14:41
IDR_RECD	434547	Process 	Biased Process		04/04/2016 11:45
IDR_RECD	434547	Process 	Breach of privacy 	04/04/2016 11:45
IDR_RECD	434547	Process 	Incorrect info		04/04/2016 11:45
IDR_RECD	434549	Process 	Delay			04/04/2016 11:53
IDR_RECD	434568	Determinations	Failure 		04/04/2016 13:55
IDR_RECD	434579	Determinations	Procedural fairness	04/04/2016 14:18
IDR_RECD	434581	Determinations	Incorrect assessment 	04/04/2016 14:31
IDR_RECD	434906	Process 	Incorrect information 	06/04/2016 09:41
IDR_RECD	434973	Determinations	Procedural fairness	06/04/2016 16:10
IDR_RECD	434973	Determinations	Failure			06/04/2016 16:10
IDR_RECD	435004	Determinations	Failure 		06/04/2016 17:09
IDR_RECD	435004	Determinations	Incorrect assessment 	06/04/2016 17:09
IDR_RECD	435004	Determinations	Incorrect information 	06/04/2016 17:09
IDR_RECD	435004	Process 	Biased Process		06/04/2016 17:09
IDR_RECD	444410	Determinations	Incorrect assessment 	28/06/2016 15:59
IDR_RECD	444410	Process 	Biased Process		28/06/2016 15:59
IDR_RECD	444412	Other Issues	Incorrect information 	28/06/2016 16:00
IDR_RECD	444491	Process 	Failure 		29/06/2016 08:12
IDR_RECD	444969	Determinations	Unable to understand	01/07/2016 13:18
IDR_RECD	445029	Determinations	Bias			04/07/2016 09:47
IDR_RECD	445029	Determinations	Procedural fairness	04/07/2016 09:47
IDR_RECD	445269	Staff Related	Discourteous		05/07/2016 14:52
IDR_RECD	445270	Staff Related	Discourteous		05/07/2016 14:55
IDR_RECD	445427	Process 	Failure 		06/07/2016 10:41
IDR_RECD	445427	Process 	Unable to understand 	06/07/2016 10:41
IDR_RECD	445473	Determinations	Bias			06/07/2016 14:10
IDR_RECD	445473	Determinations	Procedural fairness	06/07/2016 14:10
IDR_RECD	445473	Determinations	Incorrect assessment 	06/07/2016 14:10
 

Result expected: 8

Your solution worked perfectly, and although a 2 step process (using a helper column), was much easier to understand than other solutions I saw.

Thank you very much for your assistance.


Regards
Pete

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close