• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Quick way to determine total of dropdown cell unique values

Miruna

New Member
Hello everyone,

I have been trying for quite a while to find a way to automate/quickly determine the total number of unique values in a filtered (drop-down) cell. In the attached document, I have a customer who has multiple accounts associated with her personal ID. Every month, I need to process quite a few customers who have multiple accounts and therefore require special further processing.

If I use a filter and expand it on column D, I can immediately see that a record has multiple accounts. But how can I quickly find out the total of unique accounts of a customer and write that total to another column so I can use conditions to further process those with multiple accounts?

ChandooForumQ.png

Any recommendations would be highly appreciated--I spent quite a few days trying to find a solution.

Thank you in advance for your time,

Miruna
 

Attachments

  • ChandooForumQ.xlsx
    8.6 KB · Views: 0
In E2: =IF(D2<>D1,COUNTIF($D$2:$D$25,D2),"")
Copy down

Add a title in E1 as appropriate

This shows the number of duplicate Account numbers in Column E in the first occurrence of the Account Number

If you want to see it in all occurrences
In E2: =COUNTIF($D$2:$D$25,D2)
 
Hi ,

This array formula , to be entered using CTRL SHIFT ENTER , will give you the number of distinct accounts associated with the customer :

=SUM(IF($C$2:$C$25=C2,1/COUNTIFS($C$2:$C$25,C2,$D$2:$D$25,$D$2:$D$25)))

Narayan
 
In E2: =IF(D2<>D1,COUNTIF($D$2:$D$25,D2),"")
Copy down

Add a title in E1 as appropriate

This shows the number of duplicate Account numbers in Column E in the first occurrence of the Account Number

If you want to see it in all occurrences
In E2: =COUNTIF($D$2:$D$25,D2)

Thank you so much, Hui! I get the counts, now I just have to figure out how to roll them up to the personal ID level. The goal would be for me to quickly determine that this customer has 5 unique accounts. Thank you again--I really appreciate the formulas and the prompt reply.

-Miruna
 
Hi ,

This array formula , to be entered using CTRL SHIFT ENTER , will give you the number of distinct accounts associated with the customer :

=SUM(IF($C$2:$C$25=C2,1/COUNTIFS($C$2:$C$25,C2,$D$2:$D$25,$D$2:$D$25)))

Narayan

Many thanks, Narayan! I tried the array formula but I must be doing something wrong as I get decimal numbers. I did use CTRL SHIFT ENTER just as you instructed. I'll double-check my file again tomorrow to see what I may be doing incorrectly. Thank you again,

-Miruna
 
Back
Top